Alteryx designer Discussions

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

Create new column and fill data from next rows data

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:

 

A B C  Date

1 2 3   06/17/2017

1 2 5   06/25/2017

1 2 7   06/29/2017

4 5 9   06/30/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.

In case of multiple rows with same data for A and B, pick the value of C for the row with the earliest date. 

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

 

 

A B C  Date               Forward_C

1 2 3   06/17/2017           5

1 2 5   06/25/2017           7

1 2 7   06/29/2017         <Blank>

4 5 9   06/30/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.

Highlighted
11 - Bolide

Hi @rdaga! I think what you are trying to do is definitely possible in Alteryx, perhaps even in just 2 tools!

 

I would first sort your data by Column A, then B, then Date (all Ascending) using the Sort tool. This will put rows with matching data in columns A and B next to each other but more importantly, the earliest date will be first for every combination of values in columns A and B.

 

Now all you need is a Multi-Row formula tool! You can use the "Create New Field" option and name it "Forward_C". Check columns A and B in the "Group By (Optional)" configuration. Lastly, the "Expression" is simply "[Row+1:C]". 

 

In this case, using the "Group By" option means that Alteryx will look at rows 1-3 together because they share the same values for columns A and B. The "Expression" of "[Row+1:C]" will put the next rows value for column C in the new column (as long as it shares the same values in A & B because of the Group By). The Sort we did before the Multi-Row tool helps ensure the value of C in the next row is the one with the earliest date.

 

I attached a workflow performing what I described. Try it out with your data and hopefully you will be able to build onto it to work for your full data set. Let me know if I misinterpreted part of your question or if there is more to this challenge for the Community to tackle!

Highlighted
6 - Meteoroid

Spot on!

I did the same thing and it worked.

Thank you so much 

 

Cheers!

RD

Highlighted
11 - Bolide

Awesome! You're welcome, glad I could help!

Highlighted
6 - Meteoroid

Thanks a lot for all the help.

 

Can someone please help me with below example as well:

 

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.

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)

 

Thanks in advance.

Regards,

RD

 

 

Labels