Reverse order of columns and shift to left / ignore nulls

Question

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!

Roger2 · Answer

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

RJ_1988 · Answer

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.

Transpose and Crosstab_12.yxmd

Pilsner · Answer

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

New Workflow4.yxmd