Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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