Create new column and fill data from next rows data

rdaga

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.

DultonM

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!

Fill Data from Next Row.yxmd