Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-row tool but with a blank row in between

EL1988
6 - Meteoroid

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 / BlankCustomer ID
1YesCustomer_1
2No 
3No 
4YesCustomer_2
5  
6No 
7No 
8No 

 

The desired end product looks like this.

 

Input Yes / No / BlankCustomer ID
1YesCustomer_1
2NoCustomer 1
3NoCustomer 1
4YesCustomer_2
5  
6NoCustomer_2
7NoCustomer_2
8NoCustomer_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.

5 REPLIES 5
DawnDuong
13 - Pulsar
13 - Pulsar

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
6 - Meteoroid

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
11 - Bolide

@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
6 - Meteoroid

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

 

Much appreciated to you and Dawn! 😀

DawnDuong
13 - Pulsar
13 - Pulsar

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.

Labels