Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Find a particular column and select all columns before it

Mtvbqb
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

9 REPLIES 9
grossal
15 - Aurora
15 - Aurora

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

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

grossal
15 - Aurora
15 - Aurora

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

Mtvbqb
7 - Meteor

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

grossal
15 - Aurora
15 - Aurora

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

Mtvbqb
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

grossal
15 - Aurora
15 - Aurora

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.

jones-b
5 - Atom

Thanks for going that extra mile @grossal.  I found this today and it let me to solve my problem!  I actually had something slightly different though.  I needed to split my columns into thirds but with each third keep the very first column.  The names of the columns that identify where I need to split are consistent BUT I don't know how many columns will be between them.

 

for example, I have columns: beginning, a, b, c, first divider, d, e, f, g, second divider, h, i, j, k, l

 

I needed to split these into three sets

 

1:3 - [beginning] through [d]

2:3 - [a] AND [first divider] through [h]

3:3 - [a] AND [second divider] through the end

 

BUT the number of columns between [beginning], [first divider], and [second divider] can change each time 

shobhit_gupta
7 - Meteor

@grossal Hello, can we do something similar where I should be able to keep only N number of columns before/after a specified column (here 'GROSS'). Thanks in advance.

Labels