Pick Latest Group of Consecutive Rows

Question

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.

Alteryx Data.xlsx

JohnJPS · Accepted Answer

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