Doing comparison using iterative Macro

[Ranjith15]

Hi All,

I have a business logic which will have data for each quarter. If am running the job on 2nd quarter then it should pick up second quarter file and first quarter files, find the difference in value by matching the key columns in both files and generate the output which contains two quarters data. Likewise in 3rd quarter the job should compare the data between first quarter and second quarter and as well 3rd quarter and 2nd quarter and genearte cumulative output. For the 4th quarter the job should do the same as we did in 3rd quarter and in addition it should compare the data between 4th quarter and 3rd quarter. 

From my initial analysis, the above logic can be achieved through iterative macro, but am having confusion in creating iterative macro. Does anybody have any idea on achieving the above logic using iterative macro. 

[afv2688]

I would recommend you to use a dropdown menu and attach it  to a filter tool. Then modify the filter to select which quarter you wanna calculate (wheter is Q1 or Q1+Q2 etc)

My filter tool does the following: < [Quarter] IN("Q1","Q2") >

Cheers

[Thableaus]

Hi @Ranjith15 

How are your quarter files stored? Do they have a file_Q1.xlsx name? Are they in the same folder?

Cheers,

[Ranjith15]

Yes they will have _QX.xlsx but in different folders. Currently am saving the file name and paths in excel file(treating this as parameter for the workflow) and feeding those to dynamic input tool.

[Thableaus]

@Ranjith15 

Could you please post a template of what your desired output would be? It can be with fake data.

Please use an example as if we were in Q3.

Cheers,

[afv2688]

Well if you have them in different files and not the same as i thought, I would suggest you to first use the directory tool and filter for the names as I initially proposed.

The only difference will be that this time you will be filtering files instead.

Do your files share same name and columns? Otherwise this method won't work

[afv2688]

Something like this can help?

[Ranjith15]

Please find the sample data in the attachment. The workflow has to work in way to process (Q2-Q1) then (Q3-Q2) and finally union both of the records and write into output file

[Ranjith15]

Thanks for your effort, the attached workflow will pick up only the files based on quarter selection. But as per requirement it should compare the data between two quarters and write it in output . You can refer to the sample workflow. The workflow has to run three times if we are executing in third quarter.

Q1 : Q1 file - dummy file

Q2 : Q2 - Q1

Q3: Q3 - Q2

I hope macro will help us to resolve the issue. Selecting quarter will not be problem as it will be handled by below post(current previous file for each quarter : https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Reading-Multiple-files-and-processing/idi-p/...

[Thableaus · Accepted solution]

Hi @Ranjith15 

Here's an alternative to the iterative macro:

There are some notes before applying the workflow to a real situation:

- Your quarter files must be in the same folder and have a file name with a "Qnumber" at the end. Sheet Names also must be the same. That's why I used a "*" wildcard.

- Output FileName field in Input Tool must be checked

So basically what this workflow does:

- Replaces full File Name to Qnumber

- Filters the files under the current quarter and below

- Sorts dataset by ID, Group Name and FileName (desc)

- Calculates Org Value with Multi-Row Formula Tool

- One side will bring the Quarter Fields with respective values

- The other side uses another Multi-Row Formula Tool to name operations (Q3-Q2, Q2-Q1)...

- Filters Null Values of Org Value, since they are not useful

- Cross-Tab to put Org Values in columns (Q3-Q2, Q2-Q1)..

- Join Tool to put everything together (Unknown checked to deal with variation)

- Union Tool so we do a Left Join (this is useful if, for example, you are at Q1, meaning you won't do any operations).

Package with workflow iin version 2019.1 appended. Make sure you change your Input Tool to the folder where your quarter files must be. Let me know if you have questions.

Cheers,