Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Multi Row Formula

sriniprad08
11 - Bolide

Hi Team,

Hope you are well. We are trying to build a formula where

 

1) if the value column has same value like "101" for all the three rows. then it should create diff column with 1. if there is a change for example row 5 to row 7. then it should mark "0" where there is a change. In this case "Regular" is marked as 0.

 

2)Then  if the value of the diff column is 0 then it should create a column old_value and pick the previous value of the "value" column and put it inside the old_value.

 

3) The value in yellow color should be 1.

 

Please let me know how to go about this.

 

Formula

 

if [Value]!=[Row-1:Value] and [Row-1:Diff?]!=0
then 0
elseif [Value]!=[Row-1:Value] and [Row-1:ctr_key]!= NULL()
then 0
else 1
endif

 

the output should like below

sriniprad08_0-1620744526561.png

 

8 REPLIES 8
apathetichell
18 - Pollux

Hi -  there seems to be a conflict between your rules and your dataset. Your rule asks for 3 in a row but there are situations where you have a 1 when there are only 2 in a row so I'm not sure I see what that is. In the dataset you provided there is only one instance of 3 in a row - that is the very first 3. Also - should the first occurrence of a triplet receive a 1? It doesn't in your sample data.

 

It is also unclear what you mean by the "previous value of value" in your second clause. do you mean the row before. The last row which matched 3 times? Or something else?

sriniprad08
11 - Bolide

Hi @apathetichell ,

 

Thank you for the message. Sorry for not being clear. Please find attached the sample output.

1) Row 2 to Row 4 - There is no change in the "Value column" all has "101" hence the "diff" should be 1 since there is no change "all are 101".

2) Row 5 to Row 7 - There is a change at Row 7 at the "Value" it is "Regular" hence the "diff" should be 0 since there is a change after "INTERCO"

and the "old_value" it should be "INTERCO" the previous value of the column "Value".

3) Row 8 to Row 10 - Same process has before.

sriniprad08_0-1620745501624.png

 

 

 

apathetichell
18 - Pollux

O.k. - can you confirm that in your data only rows 2-4 (1-3 in alteryx) should be 1 - there are no other 3 matched patterns.

sriniprad08
11 - Bolide

Hi @apathetichell ,

Thanks for the reply.

In general rows with consecutive values should be "1" and if there is a change then it should be "0". Not only 3 consecutives but any rows. 

We can group by ctr_key and Name.

 

apathetichell
18 - Pollux

Try this - I'm still having an issues because there is only one instance where you have the same value 3 rows in a row - so there is only one instance where I'm making it as 1. Likewise - I wasn't sure if you wanted the repeated value carried into the old value column so I skipped the first non-1 row and gave it an old value of null()

sriniprad08
11 - Bolide

Hi , Thank you so much. Really appreciate your time. Actually we need to group by the ctr_key and the Name.

The output should look like this

 

1) Marked in Yellow the "Null" should be 101.

2) Marked in Yellow the "0"'s should be 1. Because "INTERCO" is the new value if you see the "Name" field it has "CONTRACTTYPE" similarly for "COUNTERPARTYREF"

sriniprad08_0-1620750100497.png

 

apathetichell
18 - Pollux

O.k. - this fixes the row situation. First entry in every group is always a 1 was the key piece of information.

 

Does first value in a set of 3 for old column get the value or should it be blank?

sriniprad08
11 - Bolide

Hi @apathetichell ,

 

Thank you so much. Very close to the actual solution. The rows with similar values can be kept blank or if possible completely removed from the dataset. Means we can delete those three rows.

 

Cheers,

Labels