Hello everyone, my first post here, be kind. I started using Alteryx and am pretty amazed by it. But I'm starting to get to more complex data manipulations I think and need some help. I have a table like this (just an example, the actual table is much bigger): IDValue1Value2Value3Value43838473[null]Yes[null]Yes2736217Yes[null]YesYes EDIT: Outcome should basicall be: IDValue1Value2Value3Value43838473YesYes[null][null]2736217YesYesYes[null] The number of Value columns is variable (Value1 to ValueN basically) and I need to shift all "Yes" values back to back next to the ID column, so I can join them with another table containg an ID value. I am too inexperienced to find a solution to this and I'm not familiar enough with formulas. Does anyone have an idea that could solve this? Thanks so much!

I'll use the workflow made by @Qiu since its more complete than mine. * The workflow adds a record id so we know the original row order and groping of the items. * Then it transposes the data using the record id and ID fields as key columns and moving all value columns into two new columns containing the original column name and the value that was there. * The top branch sorts the data so filled values are at the top and nulls at the bottom.* it then creates a new id to identify the sorted items order. * The bottom branch adds a sort id but for the original column order. * The join is then used to match the original column order, with the new sorted order.* This keeps the column name that was in position 1 for example, matched with whatever sorted value ended up in position 1, and so on * The select removes extra columns that were used to order the items since they are not needed in the output. * The cross tab at the end is then used to pivot the data back into horizontal using the original column names as the column headers, the sorted values, and grouping it by the ID and row order record id that was created in the first step. I hope that helps

Dynamically shift column values if not [null]

fbnEnergy

Hello everyone,

my first post here, be kind.

I started using Alteryx and am pretty amazed by it. But I'm starting to get to more complex data manipulations I think and need some help.

I have a table like this (just an example, the actual table is much bigger):

ID	Value1	Value2	Value3	Value4
3838473	[null]	Yes	[null]	Yes
2736217	Yes	[null]	Yes	Yes

EDIT: Outcome should basicall be:

ID	Value1	Value2	Value3	Value4
3838473	Yes	Yes	[null]	[null]
2736217	Yes	Yes	Yes	[null]

The number of Value columns is variable (Value1 to ValueN basically) and I need to shift all "Yes" values back to back next to the ID column, so I can join them with another table containg an ID value. I am too inexperienced to find a solution to this and I'm not familiar enough with formulas. Does anyone have an idea that could solve this?

Thanks so much!

Dynamic Processing

Accepted answers

All comments

SPetrie

If column names are not important, you can utilize transpose crosstab to accomplish this.

example.yxmd

Qiu

@fbnEnergy
I assume you still want to keep the original Field Name, so maybe we can do something like below.

0214-fbnEnergy.yxmd

fbnEnergy

Wow this actually worked, thank you so much!

Would it by any chance be possible to add some explanation to your workflow? It works but I dont fully understand why.

VladgOLD

bro you are legend! its workinbg!

SPetrie

I'll use the workflow made by @Qiu since its more complete than mine.

The workflow adds a record id so we know the original row order and groping of the items.
Then it transposes the data using the record id and ID fields as key columns and moving all value columns into two new columns containing the original column name and the value that was there.
The top branch sorts the data so filled values are at the top and nulls at the bottom.
1. it then creates a new id to identify the sorted items order.
The bottom branch adds a sort id but for the original column order.
The join is then used to match the original column order, with the new sorted order.
1. This keeps the column name that was in position 1 for example, matched with whatever sorted value ended up in position 1, and so on
The select removes extra columns that were used to order the items since they are not needed in the output.
The cross tab at the end is then used to pivot the data back into horizontal using the original column names as the column headers, the sorted values, and grouping it by the ID and row order record id that was created in the first step.

I hope that helps

Qiu

@SPetrie
Thank you very much for the details. It makes more sense now. 😁

flying008

Hi, @fbnEnergy

FYI.

录制_2026_02_24_15_42_06_290.gif

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317