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

Cross tab with null separators

asyraf__razak
7 - Meteor

I wanted to change this data 

unknown.png

 

Into this: unknown (1).png

 

How do I do this and the null acts as an indicator for the next row of data. This data follows up with the 2nd and the 3rd data under the first data.

6 REPLIES 6
lmorrell
11 - Bolide

Hi @asyraf__razak

 

Solution is attached. 

 

1.png

 

 

NULL delimiters are always tricky. There were two things to get this done 1) Create an automated Row ID to group all the 'A's together, all the 'B's together, all the 'C's together up to the NULL field. 2) Generate a number to indicate what position the values were in underneath the NULL. 

 

To address challenge 1, we can use the Multi-Row Formula Tool to create a Row ID with the following formula

if isnull([Row-1:F1]) OR isempty([Row-1:F1]) then [Row-1:Row ID]+1
else [Row-1:Row ID]
endif

 

To address Challenge 2, we can use the Tile Tool's Unique Value setting to create a sequence number (and thereby giving the position of the value relative to the first).

 

Once we have addressed both of these challenges, we can crosstab and group by Row ID to return the desired result.

 

Hope this helps!  

lmorrell
11 - Bolide

Hi @asyraf__razak 

 

Solution is attached. 

 

1.png

 

 

NULL delimiters are always tricky. The two challenges here are 1) Grouping relevant fields together after each NULL cell 2) Taking the position that each value was entered and putting them into a column. 

 

Challenge 1 can be addressed by using the Multi-Row Formula tool with the below formula to assign a Row ID to all values that will eventually be crosstabbed out. 

if isnull([Row-1:F1]) OR isempty([Row-1:F1]) then [Row-1:Row ID]+1
else [Row-1:Row ID]
endif

 

Challenge 2 can be addressed by filtering out null values, and then using the Tile Tool to find the position that each value was entered below the NULL cell. After both these steps, cross tabbing while grouping by Row ID will return the desired results as per example. 

 

Hope this helps! 

TonyA
Alteryx Alumni (Retired)

Is this what you're looking for? .

DiganP
Alteryx Alumni (Retired)

@asyraf__razak 

 

Here's how the logic looks like. You can use the multi-row formula tool to create the groupby logic then the crosstab tool to pivot the dataset.

Dataset.PNG

Attached is the workflow. Is this what you were looking for?

Digan
Alteryx
asyraf__razak
7 - Meteor

Yes . Thank you very much

asyraf__razak
7 - Meteor

Thanks for the description explaining the workflow. Thumbs up

Labels