Multi Row Formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Data Investigation
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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()
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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,