Multi Row - Add Specific ID into NULL

[Jocelyn1]

Hi, I imported 3 reports to Alteryx, named A, B and C respectively and all of them is starting from different ID. 

The example is shown below.

Report A starts from 400 to 404;

Report B do not have ID;

Report C starts from 100 to 103.

I would like to manually add ID for Report B (perhaps the ID starts from 1 to 6) so that later I could join it with other table. I guess Multi-Row Formula might help but I don't know how to write the formula out.

Report	ID
A	400
A	401
A	402
A	403
A	404
B
B
B
B
B
B
C	100
C	101
C	102
C	103

Appreciate if anyone could help me on this. Thanks!!!

[grossal]

Hi @Jocelyn1,

here is a quick way to do this:

Result: 

Formula:

IF isnull([ID])
THEN [Row-1:ID]+1
ELSE [ID]
ENDIF 

Workflow attached. Let me know what you think.

Best

Alex

[Jocelyn1]

Hi @grossal ,

Thank you for your quick response.

If the max number ID for Report B is only up to 3, means I would like it to start from 1 again after 3. Do you have any idea how to write the formula?

Report	ID
A	400
A	401
A	402
A	403
A	404
B	1
B	2
B	3
B	1
B	2
B	3
C	100
C	101
C	102
C	103

[grossal · Accepted solution]

sure @Jocelyn1!

Here is an update version:

IF not isnull([ID])
THEN [ID]
ELSEIF [Row-1:ID] = 3
THEN 1
ELSE [Row-1:ID] + 1  
ENDIF 

I added another condition in the Formula to handle the extra rule.

Updated workflow attached 😃

Alex