Find a particular column and select all columns before it

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

Help

Expression

Accepted answers

grossal

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.

Workflow attached.

Alex

2020-03-26-remove-columns-before-certain-column-2.yxmd

All comments

grossal

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

2020-03-26-remove-columns-before-certain-column.yxmd

Mtvbqb

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.

shobhit_gupta

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

goatley1

I'm assuming you won't have to clean up the data as much as I did so the first filter will have to be customized. Essentially you need to ensure that Set B is the first record. Sample the first record. Then we transpose the fields and test for the Set B in the value field. Use a multi row formula to find Set B and flag every record from set B while the field name is <= F33 using if then (I use 1 and 0). Drop all the records that don't pass the multi-row formula with a 1. Cross tab your field names back to columns. Then drop all records with the sampling tool. Union with the original filtered data set and keep only common fields. This should work for your situations.

Now I followed your instructions to a T this will always return the first field Fn (where n = any number form 1-33) through F33. If you meant to grab the next n fields, the you will have to tweak the multi-row formula to accumulate instead of flag. Subsequently filter for binary_test <= n. Hope this Helps

example_20250425.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703