HOW TO Cleanse and Join in Alteryx PDF

Preview

Summary

ACC 300 Susan...

Description

HOW TO: Cleanse and Join in Alteryx ACC 300 Financial Statement Technology Project Complement to the HOW TO VIDEOS on cleansing and joining files in Alteryx:   

Cleanse client and IRS data files separately. Join these cleansed files with their respective column headers files. Now join the Luna file with the IRS file into one file that will be exported and then inported into Tableau for analysis.

Alteryx Tools are noted in bold/red. Cleanse IRS data file: 1. Eliminate 4 records (rows) at the top of the file and any extraneous rows with notes in them at the bottom of the file. They don’t add value to the analysis. SAMPLE, INPUT DATA 2. Join the IRS data file with the IRS column header file. DYNAMIC RENAME 3. Eliminate rows that have totals in them. Those can easily be created in Excel and Tableau. FILTER 4. Eliminate any rows that have “nulls” in some fields where dollar values should be. FILTER 5. Eliminate columns that have all “nulls” in them. They don’t add value to the analysis. SELECT 6. Add the prefix “IRS_” to any field (column) in the IRS file so those data are clearly distinguished from client data in the merged file. SELECT 7. Review all fields to be sure they are identified with the proper data type: V_String for alpha and Double (precision) for numeric. 8. Convert the fields that have monetary data in thousands to the absolute number; that is, multiple the given number by 1000. MULTI FIELD FORMULA NOTE: Keep track of the count of records and fields in the Results section of Alteryx so you know you have eliminated the number of rows or columns that you expected to eliminate. Cleanse the client (Luna Corp.) data file: 1. Eliminate 6 records at the top of the file that don’t add value. INPUT DATA, SAMPLE 2. Combine the Client Data Column Header information with the data file. DYNAMIC RENAME 3. Eliminate “null” records at the bottom of the file. FILTER 4. Look for red triangle or yellow signals in the data and eliminate trailing white spaces. DATA CLEANSING 5. Bring in the Tax Rate worksheet information and Join it with the client data file. JOIN (then, FIND AND REPLACE)

11/4/20

6. Examine the Country of Tax Jurisdiction in the client data file to see if it agrees with the data provided in the IRS data file. [Note: not a problem in our file, but be prepared when joining files to check country names for agreement; e.g., Chengdu, Shanghai, Suzhou to China, South Korea to Republic of Korea (South), United Kingdom to United Kingdom (England, Northern Ireland, Scotland, Wales) JOIN (then, FIND AND REPLACE)

Merge the IRS data file (now with proper column headers) and the client data file (now with proper column headers). Only include the IRS data file countries that match up to the client data file countries in which it does business.

11/4/20...