Cross tab with null separators

[asyraf__razak]

I wanted to change this data 

Into this: 

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.

[lmorrell · Accepted solution]

Hi @asyraf__razak, 

Solution is attached. 

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]

Hi @asyraf__razak 

Solution is attached. 

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 · Accepted solution]

Is this what you're looking for? .

[DiganP · Accepted solution]

@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.

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

[asyraf__razak]

Yes . Thank you very much

[asyraf__razak]

Thanks for the description explaining the workflow. Thumbs up