Selecting one column at a time macro

[tuomassalminen]

Hello,

I'm a beginner with Alteryx designer (and especially macros). I have been developing a workflow that uses a database of monthly variables. These variables would run through a process which creates lagged variables for 12 months for statistical analysis purposes.

Right now the lag component works when one column is manually selected. It is based on a Lag macro from the community with some modifications mainly to have unique names instead of generic Lag-1, Lag-2 etc.

Right now the problem is that I need a macro which would choose one column at a time and go through multiple (upwards of hundreds) variables instead of the current process of manually using select tool. At least as an idea this part seems quite straightforward, however I tried to implement it by myself and looked for solutions from the community but couldn't find anything.

Can someone help me? If there are problems with the file or the macros involved, please let me know.

[T_Willins · Accepted solution]

Hi @tuomassalminen,

Batch macros work well when working with single columns.  I was having difficulty with your workflow and downloading the lag macro, so I mocked up how this might work.  The batch macro uses a Dynamic Select tool to select a single column.  The Control Parameter and Action Tools run each column through sequentially.  The Transpose tool at the end of the macro (and associated Cross Tab tool in the workflow) is needed so the data doesn't union in a non-usable format at the end of the macro.

[tuomassalminen]

Thank you for your reply!

In my original workflow I had a process which created Lags of 12. Even higher numbers would be possible but for my analysis 12 is enough. In your workflow it iterates through all variables and creates a lag function for each column. I'll try to update my existing workflow or revise yours to have similar process as yours but with 12 lags. 

I will try to implement this by myself but if someone knows how to do this, feel free to post it. I will let you know if I manage to solve it by myself.

[tuomassalminen]

Thank you again T_Willins,

Managed to solve the original problem based on this solution. Basically, I made some tweaks to the middle part of the macro which creates the Lagging effect. I replicated that 12 times to have up to 12 lags for each variable. With some small tweaks to the existing (and replicated) macro elements it now works. I know using a macro would have been possibility, instead of having 12 identical processes. However, as a beginner I'm satisfied with a solution that works.

If someone is interested in seeing the final workflow, let me know.