Equivalent of multirow formula from Alteryx Designer

[ChrisGoodman]

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?

[APrasad_Tri]

Hi @Chris Goodman? 

Thank you for reaching out. We can do that using the below steps:

Step1: assign 1 to empty rows.

IF(ISMISSING([{Col 1}]), 1, 0)

Step 2: Perform ROLLINGSUM() on the new col. Add 1 to it to create new IDs.

ROLLINGSUM(column1, -1, 0)ADD(ROLLINGSUM(column1, -1, 0), 1)

I have merged 2 steps into 1.

Step 3: Use Prefix transform to add ID in the new column2

Hope this helps.

Best,

Apeksha Prasad