Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to create a new column by looping through rows

Highlighted
6 - Meteoroid

Hi Team,

 

I am new to alteryx. I am looking for the operation to be done on my data.

I want to create a new column based on the comparison of data with other rows.

For example: If my data set is:

 

Initial Data:

A B C  Start Date      End Date

1 2 3   06/17/2017     06/26/2017

1 2 5   06/25/2017     06/28/2017

1 2 7   06/29/2017     06/30/2017

4 5 9   07/01/2017     07/02/2017

 

I want to create new column for which I will first compare data in Column A and B.

If data in current record is equal to the data in next rows for column A and B (in this case for column A and B, for first row, second and third row matches) then I want to further check for the data in column C which is to be copied in newly created column.

This part I completed using Sort and Multiple row tool.

 

 

In case of multiple rows with same data for A and B, pick the value of C for the row with the earliest date and Start Date of the next row should be greater than End Date of the current row. (if we can use something like loop, where I will exclude data in C if that row's start date is less than the current rows end date)

So I have to create the new column(Forward_C) in which I will have forward values(next rows) of C.

 

Expected result:

 

A B C  Start Date      End Date       Forward_C

1 2 3   06/17/2017     06/26/2017        7

1 2 5   06/25/2017     06/28/2017        7

1 2 7   06/29/2017     06/30/2017       <Blank>

4 5 9   07/01/2017     07/02/2017       (Compare first column A and B with next rows, if they are equal take value of C in Forward_C)

 

Please let me know if this operation is possible in Alteryx.

Kindly let me know for further clarifications.

 

Regards,

RD

Highlighted
14 - Magnetar
14 - Magnetar

Give this workflow a shot... I think it can be done with a Multi-Row tool & some creative sorting...

 

1. Add a RecordID

2. Sort Descending by RecordID

3. Multi-Row tool: Create New Field "Forward_C", Group By A & B, and use the following formula to determine the value:

 

If [Row-1:Start Date]>[End Date] Then [Row-1:C] Else [Row-1:Forward_C] Endif

 

4. Sort Ascending by RecordID

 

Does that accomplish what you're looking to do? Please let me know, hope that helps! 

 

Cheers,

NJ

Highlighted
6 - Meteoroid

Hi Nicole,

 

Thanks a lot for your solution.

However, with this approach I think we will not be able to fetch the record accurately.

What if start date of row in else is less than the End date.

 

Do we have some dynamic approach which will fetch exactly that C data for which the Start Date of that row will be greater than End Date of current row.

 

Please let me know for more detailed elaboration.

 

Thanks,

RD

Labels