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.
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
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.
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
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.
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
@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.
User | Count |
---|---|
17 | |
16 | |
13 | |
6 | |
5 |