Multi-row tool but with a blank row in between

[EL1988]

Hi all,

I have an existing data table that looks like this. If the Middle Column is a No, then I would like to fill in the Customer ID Column with the last Customer ID.

Input	Yes / No / Blank	Customer ID
1	Yes	Customer_1
2	No
3	No
4	Yes	Customer_2
5
6	No
7	No
8	No

The desired end product looks like this.

Input	Yes / No / Blank	Customer ID
1	Yes	Customer_1
2	No	Customer 1
3	No	Customer 1
4	Yes	Customer_2
5
6	No	Customer_2
7	No	Customer_2
8	No	Customer_2

The presence of the empty row in Row 5 means that I was unable to use the Multi-row tool with expression (IF ISEMPTY([Customer ID]) THEN [Row-1:Customer ID] ELSE [Customer ID] ENDIF) because looking back one row is not sufficient when there is an empty row.

Any guidance on how I can go about solving this? All inputs are much appreciated.

[DawnDuong]

hi @EL1988 

Almost there, you can do this in 2 steps and you have already done step 1

Step 1: (IF ISEMPTY([Customer ID]) THEN [Row-1:Customer ID] ELSE [Customer ID] ENDIF) 

Step 2: Update the [CustomerID] column as follow:

if isempty([Yes / No / Blank]) then null() else [CustomerID] endif

Step 2 add a "corrective" step to force any row where the [Yes / No / Blank] is empty to have the [CustomerID] to be empty as well.

Cheers,
Dawn.

[EL1988]

Thanks Dawn, but I realised that there are instances when there are also multiple blank rows in between. Hence it still doesn't work when it refers back to 1 line before.

I guess that complicates Step 1?

Thank you.

[Peachyco · Accepted solution]

@EL1988 :

Here's what I'd do:

1. Put a unique ID to the table. If the "Input" column is unique, I'd just use that. Otherwise, I'd use the Record ID tool.
2. Use a Filter tool to remove the blank rows.
3. Use the Multi-Row Formula tool like you described.
4. Use the Union tool to bring the blank rows (from Step 2) back.
5. (Optional) Use the Sort tool on the RecordID column to return the table to its original order.

I think that, in this way, you might be able to handle any number of blank rows. 😊

[EL1988]

Thanks Peachyco, got it to work thanks to your suggestion.

Much appreciated to you and Dawn! 😀

[DawnDuong]

hi @EL1988 

Actually the Multi-Row tool works from top to bottom. So even if you have instance of multiple blank rows in between, that [Row-1] reference still work.

Suppose that you have data on row 1 and blanks on row 2 and 3.

Because the Multi-Row works from top to bottom, the row 2 gets its value first from row 1, then the row 3 gets its value from the now filled-up (previously blank) row 2.

For Alteryx, it's better if you drag a Text input tool to the canvas, connect it to the Multi-Row to see how the various conditions work out. That will my it less "dry" and easier to remember.

Dawn.