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

Pick Latest Group of Consecutive Rows

superjesse
8 - Asteroid

Hello there,

 

I've been searching through the community and I just can't seem to figure out my issue. I have sets of data by a unique ID. I then calculate when the rows are consecutive. For example, ID 1258 could have 4 sets of rows that are consecutive. I only want the most recent. I just want the records highlighted in green, and maybe add a column that identifies which ones qualify. Ultimately this data will be transposed with the date across the top.

 

My criteria is to only bring in the House ID if it is one day behind today(datetimenow), and has more than one consecutive day. Thanks in advance for any help. This community is so helpful.

 

Capture.PNG

2 REPLIES 2
JohnJPS
15 - Aurora

Hi @superjesse,

If I sort by HouseID and then Date descending, I can get the desire result using a MultiRow Formula with:

IF ([House_ID] != [Row-1:House_ID]) THEN
  "Y"
ELSE
  IF ([Consecutive_Days] < [Row-1:Consecutive_Days] AND [Row-1:MostRecent] = "Y") THEN
    "Y"
  ELSE
    "N"
  ENDIF
ENDIF

... where "MostRecent" is the new field name I'm calculating with the MultiRow Formula.

(See also, attached workflow).

 

Hope that helps!

 - John

superjesse
8 - Asteroid

Amazing. Love this community - it worked perfectly.

 

Thank you very much.

Labels