Reverse order of columns and shift to left / ignore nulls

[Roger2]

Hi,

I have a table representing a hierarchical reporting structure, and I need to reverse the order of the levels for each row. For example, a row like this:

Apple -> Orange -> Pear -> Plum

Should be transformed into:

Plum -> Pear -> Orange -> Apple

The table has multiple rows, with varying lengths of hierarchy (some levels are Null). Here's an example of my input and desired output:

What’s the best way to achieve this in Alteryx? I’m looking for a scalable solution since the number of levels may vary.

Thanks in advance!

[Pilsner]

Hello @Roger2 

I have attempted to solve this challenge primarily through the use of pivoting the data. Here is a brief description of the key steps:

Once pivoted, I filtered out any null rows and found the maximum "Order number" per group. I.e for the first row, max order = 4.

Using this, I then did the maximum order (per original row) - the order number, which reverses the order. eg 

In row one, Apple has order 1 (as it's in column "L1") so we go 4-1 to get its new order number = 3. (As you start counting from 1, not 0, we have to add 1 back onto all final order numbers).

I appreciate my description may be hard to follow, so I have tried to annotate each tool in the screenshot below.

I have also attached the workflow below. 

Please let me know how you get on or if you would like me to explain anything further.

Regards - Pilsner

 

[RJ_1988]

Hi,

Please find the attached workflow for your solution.

I have used a mix of transpose/crosstab and using a record ID in between to sort.

Please mark it as a solution if this solves your use case.

Thank you.

[Roger2]

Thank you both - I will try these out today and let you know!