I am trying to calculate a new field to assign a unique ID to a set values but to add one to the unique ID when there is a break (e.g null records).
For example if I have a dataset which goes
100
101
102
103
104
105
I would like all number 100-102 grouped to ID1, 103-104 as ID2 and 105 as ID3
In Alteryx Designer you can use a multirow formula tool which refers to itself, for example
IF Isnull(F1) THEN [row-1:ID]+1 ELSE [row-1:ID] ENDIF
however when creating a new column in Trifacta it won’t allow me to reference column ID in the expression editor.
IF(Isnull(F1),prev([ID],1)+1,prev([ID],1))
Any tips to achieve the desired outcome?