Dynamically select column based on information from another column

[stephouyang]

Hi, 

I'm trying to select all the columns that end in the value of the last column. How can I do that? 

My last column is dynamic, for period 1 it will show _1, period 2 it will show _2, etc.

When it's period _1 (which is the last column in the input file), I want to select 2 columns "PERIOD_1" & "C_PERIOD_1".

When it's period _2 , I want to select 2 columns "PERIOD_2" & "C_PERIOD_2".

Attached a sample input file.

Many thanks! 

[flying008]

Hi, @stephouyang 

Maybe the dynamic select solution is just your want:

BTW, you select condition is by last column' name or the end-line value of last column? 

[Yoshiro_Fujimori]

Hi @stephouyang ,

I understand you want to specify period "dynamically" specify from other input.

So I used a macro as below. If you want to make it a drop-down list, that would be also easy.

Workflow

Macro

Input data for "Period"

Output

[flying008]

Hi, @Yoshiro_Fujimori 

Dear Sir, IMHO, Crosstab tool involves the operation of field names. If the field names of the current data contain special characters like ~^&*()[], it is likely that the expected results will not be obtained, so corrections or other solutions are required to handle the needs of this scenario.

[Yoshiro_Fujimori]

Hi @flying008 ,

Thanks for the comment. You are correct that the issue can happen if the column names contain special characters.

Your solution also includes Cross Tab tool.

Does it work when the data contains special characters in column names? (The Union tool does the trick?)

If possible, could you attach your workflow?

Hi @stephouyang ,

After review of the original question, now I understand what you mean by "the last column".

So I corrected my workflow as below.

[Yoshiro_Fujimori]

Hi @flying008 ,

I added some tools to deal with special characters, and a bug fix to the main workflow.

Thanks for the suggestion. It was fun.

Main workflow

Macro

Input

Output

[flying008]

Hi, @Yoshiro_Fujimori 

Dear, thank you for your share.

As my workflow, my cross tab tool is a special version by customized, it can retained all original field name.

So, I building a trick for get same output by dynamic rename.

[ArtApa]

Hi @stephouyang - Here is one more solution:

[Yoshiro_Fujimori]

@ArtApa ,

This use of Multi-Field Formula and Data Cleansing is cool !!

Thank you for sharing.

[SPetrie]

Just adding another variation of a solution.

You can use a dynamic rename to update the description of the columns you wish to keep and then dynamic select to select only those.