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

[florijn]

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:

No	Date	Shift
A	1/02/2020	DS
A	2/02/2020	DS
A	3/02/2020	DS
A	4/02/2020	DS
A	5/02/2020	DS
B	1/02/2020	NS
B	2/02/2020	NS
B	3/02/2020	NS
B	4/02/2020	NS
B	5/02/2020	NS
C	1/02/2020	DS
C	2/02/2020	DS
C	3/02/2020	NS
C	4/02/2020	NS
C	5/02/2020	NS

Required Result:

No	Date	Shift	Shift n-1	Shift n-2	Shift n-3	Shift n-4
A	1/02/2020	DS
A	2/02/2020	DS	DS
A	3/02/2020	DS	DS	DS
A	4/02/2020	DS	DS	DS	DS
A	5/02/2020	DS	DS	DS	DS	DS
B	1/02/2020	NS
B	2/02/2020	NS	NS
B	3/02/2020	NS	NS	NS
B	4/02/2020	NS	NS	NS	NS
B	5/02/2020	NS	NS	NS	NS	NS
C	1/02/2020	DS
C	2/02/2020	DS	DS
C	3/02/2020	NS	DS	DS
C	4/02/2020	NS	NS	DS	DS
C	5/02/2020	NS	NS	NS	DS	DS

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

[DavidP · Accepted solution]

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.

No	Date	Shift	Name	Value
A	01/02/2020	DS	Shift N-1
A	02/02/2020	DS	Shift N-1	DS
A	03/02/2020	DS	Shift N-1	DS
A	04/02/2020	DS	Shift N-1	DS
A	05/02/2020	DS	Shift N-1	DS
B	01/02/2020	NS	Shift N-1
B	02/02/2020	NS	Shift N-1	NS
B	03/02/2020	NS	Shift N-1	NS
B	04/02/2020	NS	Shift N-1	NS
B	05/02/2020	NS	Shift N-1	NS
C	01/02/2020	DS	Shift N-1
C	02/02/2020	DS	Shift N-1	DS
C	03/02/2020	NS	Shift N-1	DS
C	04/02/2020	NS	Shift N-1	NS
C	05/02/2020	NS	Shift N-1	NS

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.

[florijn]

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]

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:

No	Date	Shift	Shift n-1	Shift n-2	Shift n-3	Shift n-4
A	1/02/2020	DS
A	2/02/2020	DS	DS
A	3/02/2020	DS	DS	DS
A	4/02/2020	DS	DS	DS	DS
A	5/02/2020	DS	DS	DS	DS	DS
B	1/02/2020	NS
B	2/02/2020	NS	NS
B	3/02/2020	NS	NS	NS
B	4/02/2020	NS	NS	NS	NS
B	5/02/2020	NS	NS	NS	NS	NS
C	1/02/2020	DS
C	2/02/2020	DS	DS
C	3/02/2020	NS	DS	DS
C	4/02/2020	NS	NS	DS	DS
C	5/02/2020	NS	NS	NS	DS	DS

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

[florijn]

Solution to the required result found.

[DavidP]

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]

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