Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Easiest way to move nth rows up from the active row

Highlighted
8 - Asteroid

I have a data set with period values like 201901 for Jan 2019, 201902 for Feb 2019 and so on, the raw data is updated in a column called period start date, below is the input data screen shot

Raw.PNG

Now I want to move 'N'th row up in 'Start Period' column to copy the value and update as 'End Period', so my formula should be like

 

Create 'End Period' 

if [Latest Period] = [Period Start]
then move 11th rows up in Period start column                                      
else [Latest Period]
endif

 

Desired output would be like this

 

Output in case "Latest Period - 11th row up"                         Output in case "Latest Period - 7th row up"

Output.PNG                                Output.PNG

 

[Row-1:Period Start] in multi row tool takes me to active row -1 but how do I move multiple rows up as shown above, any easy method?

 

Thanks

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @saibal_78 if you know what row you are going to such as in your example the 7th row in the Multi-Row formula tool you can increase the number of from 1 to 7 such as my example below.

 

Multi_Row_040919.PNG

Highlighted
Alteryx Certified Partner

Hey @saibal_78 

 

I think it would be easier doing this as dates rather than trying to lookup previous rows.

 

First you could convert you columns to dates: DateTimeParse(LatestPeriod, "%Y%m")

 

Then you can create your end period column DateTimeAdd(LastestPeriod, -7, "months")...or make this 11, 12 whatever you need

 

Then to make it look like the original input format you can use DateTimeFormat(EndPeriod, "%Y%m")

Highlighted
8 - Asteroid

Awesome ...thanks a ton

Labels