Organizing Financial Report Data Overview

Organizing Financial Report Data

Parsing and Organizing Multiple Files with a Wild Card

Overview

Many times reporting processes generate multiple reports with the same structure but related to different entities or time periods. The same report could be generated for each month, or for a different region or business segment. It is common to combine these reports into one analytic process to get a better understanding of the business’s performance. In Alteryx, multiple files can be pulled in simultaneously using wild cards when the files are in the same format, and in the same location.

Objective

Given three formatted financial reports, use a wild card to combine the files together before cleansing and organizing the data into a tabular structure for future analysis.

Processes

1. Pull in all three files using a wild card, add a unique record identifier, and update field names.

2. Pull “Source” values into their own field and fill. (Source example “3692 US- Mock USA”)

3. Pull “Category Numbers” into their own field and fill. (8 digit values)

4. Pull “Categories” values into their own field and fill. (Always provided on the same record as a category number)

5. Replace # with 9999 and pull “Partner Numbers” into their own field. Remove records without a partner number, then remove and reorganize fields.

Data Dictionary

Reconciliation Balance Sheet 1.xlsx

The other two balance sheets have the same format. In the input configuration window, check option 6, “First Row Contains Data”

Desired Output Format