Alteryx Finance Questions!

Question

Hi Alteryx family

I am new to Alteryx and have been scrolling through the Community which as been super helpful so far.

I am trying to do the following steps but am getting stuck:

1. Extract data for Balance Sheet and Income Statement from Specific Columns (a large consolidated report with 1000 companies, we only require 30 of them for our reporting)

2. Create Separate Tabs in one Workbook with Relevant Output (for Balance Sheet (BS) and Income Statement (IS))

3. Add New Rows into Relevant output for BS and IS Adjustments

4. Maintain source Formatting and Formulas

5. Perform checks back to Source data once final output complete.

Below are the areas I am getting stuck:

1. In my 'Output file' most of the Descriptions are summarised correctly into one tab with the relevant balance, however Alteryx seems to be putting anything split by '- Intercompany' or  '-third party' (in the Description Column) in its own Separate tab.

The Descriptions for Row 2 and 3 have a space followed by a hyphen then 'intercompany' or 'third party' - Alteryx picks up all the Columns correctly in my output, but skips all the '-intercompany' and '-third party' splits and puts them into Separate tabs, so I have about 40 lines split into separate tabs and then my remaining 1,900 lines sitting in my main tab. I am not sure if I clicked a setting to do this or this is automatic from Alteryx as a delimiter.

Using the 'Select' took I have the Description type as 'V_string' - I am not sure if this is correct, or what Type we should use for Text.

3. - Once the data has been extracted correctly using the 'Select' tool, how do we

a. add in new Columns; and

b. Add formulas to these new columns; or

b. get these to feed through to an 'adjustments' Workbook I upload? I would preferably get my adjustments Workbook added as another tab in the Output file and have formulas in the new adjustment columns directly feed to this.

4. - Everything in the Output file appears to be in CSV formatting with no Formulas. The formulas in the source document should preferably be maintained.

Note: In the Output file I have already ticked 'Preserve Formatting on Overwrite' and put Output Options as 'Overwrite Sheet or Range'

I could easily create a template to be populated every month and use this, however often new lines will be added from the Source Workbook, so I would prefer if we could somehow maintain source formatting.

5. I am not sure how to perform a check back to our Source workbook in Alteryx. E.g. Sum of 30 companies mentioned above should equal to total of 2 different Columns in Source workbook. I would create a 'checks' tab within my Output file back to the Source Data originally uploaded.

I realise I got carried away with questions. Any help or guidance for any of these steps would be much appreciated!

Thank you

PanPP · Answer

Hi @NadiaFS

If you right-click any tool at the top within Designer, then click Example or Help-

--> This will provide a workflow that contains the tool explaining the logic.

--> The help link will provide documentation explaining the functionality of the tool.

Issues:

1) If you are bringing in a .csv file, check option 5 to see if there is a delimiter listed there.

Are you bringing in one column of data? Would you be able to provide a screenshot of the behavior or upload sample data?

2) In the select tool, you can use String or V_Wstring for text and double for numeric values. Please see this link for data-type explanations.

3) One way to add formulas or columns inside Alteryx is to use the Formula tool.

You can simply click the drop-down in the configuration window (left side of Designer) and click add a column,

- There are many functions inside the Formula tool found here

4) You can change the output option to a different file format if you select the dropdown next to File Format.

You can also output to a different sheet to preserve your original formulas within Excel.

Alteryx will not be able to output all the formulas into the tabs of an Excel file.

5) In order to create a check, you can build out another workflow and perform the audit/check, then output it to your checks tab like you mentioned.

For additional workflow practice, you can check out our Weekly Challenge Index.

I hope this helps. If it does, please like the post, if it answers all your questions please mark it as a solution.