Dynamic Select to Start Importing Columns with Specific Name

[Shanlim]

Hi, 

I was trying to build a workflow in my macro that can helps to pull the info from different listing from different countries. I would also like to make it as dynamic as possible as it will work for different type of listing. I had identified that I only want to start importing the column that has a code started with "P". However, I had facing the problem as the number of columns before the columns I need is always different. I had tried to look at the dynamic select tool, but not really sure how it works. 

Is there any kind soul that can advice me on this?

Thanks a lot in advance!

[AngelosPachis]

Hi @Shanlim ,

When you say you only want to keep columns that have a code starting with "P", is that P the first letter in that column header?

In the dynamic select tool, you have two options, the first being to keep only the columns of a certain data type (Byte, String, V_String and so on) and the second is to keep only the columns that fulfill a certain condition, which can be via a formula.

In your instance, if the columns you want to keep start with a "P", then you can define a formula that says:

StartsWith([Name], "P")

which would check if the field name starts with "P" and if it does it will keep that column. An alternative would be:

Left([Name], 1)="P"

which would only check if the first character in your field names from the left hand side is a "P".

I've mocked up an example for you and I have created those 3 fields where only two start with a P and only the first one contains a code

 If you use any of the above formulas, the dynamic select tool would keep the first field (which is a code) and the third field, which also starts with a P but it does not contain code information.

So that means that the dynamic select partially works, but still not perfect because Parameter_4 field which in  my example is not a code is still in my dataset. You have to refine this further, based on the column headers of your dataset, maybe RegEx might help as well.

Can you provide an example of the codes in the column headers and maybe that can help us identify a pattern?

I'm also sending attached a workflow from Alteryx examples on how to use the dynamic select tool.

Hope that helps,

Angelos

[danilang]

Hi @Shanlim 

If you want to select the first "P" column and all the following ones, you need some way to mark the columns that you want to keep so the Dynamic Select can determine which ones to keep.

Use a Field Info tool which returns column info as rows.  The mark the columns that you want to keep using a Multi-Row tool  and use a filter to keep only the marked ones.  Add a NewName to use in the Dynamic Rename to rename only the columns to keep.  The Dynamic Select keeps only the marked columns and the second Dynamic Rename restores the original column names

Input

  

Output

  

Dan

[Shanlim]

Hi @danilang, 

Thanks for the swift reply!

Apologize as I guess I had not made my question clear enough. Actually the column with the "P" code is part of my data, and not the header. 

My initial input is as below. 

However, the column before the code started with "P" might not always be blank or null and there might be some other values in the columns, and eventually I only want to start import those column with the "P" onwards. Also, I would like to keep the roll above the first cell that started with "P" code as it is usually my header. My intended output is as below. 

I had added a few tools from your proposed workflow as attached and it seemed to work. Perhaps can get some advice from your if there is any other better way than this?

[Shanlim]

Hi @angelospachis, 

Thanks for the swift reply!

Apologize as I had not made my question clear enough. 

I had rephrased my question in another comment and actually I want to keep the columns that has the "P" code and the subsequent columns as well. 

By the way, thanks for the clarification on the dynamic tool and I had learnt something too!

Cheers!!