Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Find a particular column and select all columns before it

7 - Meteor

Hello,

 

I have a data set where the number of columns will shift. I know that I want to keep column "GROSS" and all of the columns before it.

 

I'm looking into the Dynamic Select tool, and have been able to select the "GROSS" column.

 

FINDSTRING([Name], "GROSS")>-1

 

I'm at a loss on how to select the columns before it as well. Could I somehow determine the FieldNumber of the "GROSS" column and then do a select FieldNumber

Highlighted
12 - Quasar

Hi @Mtvbqb,

 

I am not sure if it's the best way, but I'd do it differently.

 

Steps:

1) Shift Columns to rows (Transpose Tool)

2) Multi-Row-Formula to determine all values before 'Gross' and adding a 'Keep' Column.

3) Filtering all unneeded columns (Filter Tool)

4) Shifting everything back (Cross Tab)

 

grossal_0-1585243919422.png

 

I have tried it with two different examples and it worked well. I'll attach my workflow for you. Let me know if it helps.

 

Best 

Alex

Highlighted
7 - Meteor

Hey @grossal

 

Thank you so much for the suggestion! Unfortunately, I gave it a shot and didn't produce the expected results.

 

The data isn't correctly retained after either the transpose or the transform.

Highlighted
12 - Quasar

Ahh, I see my error.

 

I have adapted the workflow. I am struggling a bit with shifting the columns back tho.

 

grossal_1-1585245755906.png

 

I'll try to figure it out while getting some food.

 

Current version is attached.

 

Alex

Highlighted
7 - Meteor

Apologies for the delay, I had to mock up some data. The attached is representative of the actual data.

Highlighted
12 - Quasar

I think I finally got it. I tried another approach this time. I should have gone with your first guess (Dynamic Select) in the beginning and we would have been quicker.

 

grossal_0-1585248056071.png

 

 

Workflow attached.

 

Alex

Highlighted
7 - Meteor

Dude,

 

That totally worked!!! Thank you so much!

 

One tiny edit, the formula on the Multi-Row Formula tool was slightly off

IF [Row-1:Keep] = 'No' OR ([Row-1:Name] = 'GROSS' AND [Name] != 'GROSS')
THEN 'Remove'
ELSE 'Keep'
ENDIF

 

Since it was looking for 'No' but the formula was set to output 'Remove'. I simply changed the 'Remove' to 'No' and it worked like a charm!

 

IF [Row-1:Keep] = 'No' OR ([Row-1:Name] = 'GROSS' AND [Name] != 'GROSS')
THEN 'No'
ELSE 'Keep'
ENDIF

Highlighted
12 - Quasar

I am glad we finally figured it out!

 

Ohh, looks like I forgot to change that. I'll edit my post and attach a fixed version in case someone will find this post in the future.

Labels