Find a particular column and select all columns before it
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Expression
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Apologies for the delay, I had to mock up some data. The attached is representative of the actual data.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
