Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to use macro to add columns with value of last row

florijn
7 - Meteor

Hi, I a new one to Alteryx Macro. I'd like to add new columns to the original ones by macro as follows.

 

Original Columns:

NoDateShift
A1/02/2020DS
A2/02/2020DS
A3/02/2020DS
A4/02/2020DS
A5/02/2020DS
B1/02/2020NS
B2/02/2020NS
B3/02/2020NS
B4/02/2020NS
B5/02/2020NS
C1/02/2020DS
C2/02/2020DS
C3/02/2020NS
C4/02/2020NS
C5/02/2020NS

 

Required Result:

NoDateShiftShift n-1Shift n-2Shift n-3Shift n-4
A1/02/2020DS    
A2/02/2020DSDS   
A3/02/2020DSDSDS  
A4/02/2020DSDSDSDS 
A5/02/2020DSDSDSDSDS
B1/02/2020NS    
B2/02/2020NSNS   
B3/02/2020NSNSNS  
B4/02/2020NSNSNSNS 
B5/02/2020NSNSNSNSNS
C1/02/2020DS    
C2/02/2020DSDS   
C3/02/2020NSDSDS  
C4/02/2020NSNSDSDS 
C5/02/2020NSNSNSDSDS

 

I can use several steps of Multi-Row Formula to achieve the results. But I was stuck in the iterative marco to repeat creating the new columns with [Row-1:Shift] dynamically. Any idea to achieve the result by marco?

 

Thanks

6 REPLIES 6
DavidP
17 - Castor
17 - Castor

Hi @florijn 

 

There's one snag with doing this with an iterative macro (and this is something new I learnt today) - the columns of the output of an iterative macro is set during the 1st iteration.

 

This means that if you're trying to add 1 new column at every iteration (Shift N-1, N-2, etc), this won't work.

 

The trick is therefore to generate the new columns in a transposed state. What does this mean? Well, the output of the iterative macro should look like this. Every iteration then adds a new set of 15 rows (as per the data in this example). After the macro you can then us a crosstab tool to get the data in the correct format, as shown below.

 

NoDateShiftNameValue
A01/02/2020DSShift N-1 
A02/02/2020DSShift N-1DS
A03/02/2020DSShift N-1DS
A04/02/2020DSShift N-1DS
A05/02/2020DSShift N-1DS
B01/02/2020NSShift N-1 
B02/02/2020NSShift N-1NS
B03/02/2020NSShift N-1NS
B04/02/2020NSShift N-1NS
B05/02/2020NSShift N-1NS
C01/02/2020DSShift N-1 
C02/02/2020DSShift N-1DS
C03/02/2020NSShift N-1DS
C04/02/2020NSShift N-1NS
C05/02/2020NSShift N-1NS

 

The macro and workflow look like this. The last iteration of the macro produces a column where all the values are empty, so I had to use a Tile and Filter tool after the macro to remove this before crosstabbing it all back. 

 

Workflow and macro attached.

 

DavidP_0-1600812600143.png

 

DavidP_1-1600812622986.png

 

 

 

 

 

 

florijn
7 - Meteor

Thanks. It does work, but the workflow is much more complicated than what I thought. In python pandas it is very easy loop code including a variable in both column name and shift method. Hope Alteryx can be more dynamic.

florijn
7 - Meteor

Hi @DavidP

 

Sorry, just found your answer for No C is different from what is required, like on shift n-4 should be DS not NS in your solution.

 

Required Result:

NoDateShiftShift n-1Shift n-2Shift n-3Shift n-4
A1/02/2020DS    
A2/02/2020DSDS   
A3/02/2020DSDSDS  
A4/02/2020DSDSDSDS 
A5/02/2020DSDSDSDSDS
B1/02/2020NS    
B2/02/2020NSNS   
B3/02/2020NSNSNS  
B4/02/2020NSNSNSNS 
B5/02/2020NSNSNSNSNS
C1/02/2020DS    
C2/02/2020DSDS   
C3/02/2020NSDSDS  
C4/02/2020NSNSDSDS 
C5/02/2020NSNSNSDSDS

 

Anyway, your solution gives me a hint how to deal with it. Thanks.

florijn
7 - Meteor

Solution to the required result found.

DavidP
17 - Castor
17 - Castor

Good spot @florijn,

 

I had [Shift] = [Value] in an earlier version and forgot to add it back in.

 

It will be an interesting project to build the solution with the Python tool.

florijn
7 - Meteor

Here is the workflow with python tool. very easy steps to achieve the required result.

Labels