Dynamically Select Through Mapping Table

[Zona]

Hi community,

I need to extract specific fields from the original dataset based on a "Field List." For example, Table 1 requires fields B, C, D; Table 2 requires fields E, F; and Table 4 needs to rename certain columns before selecting them.

In essence, I want to create a dynamic version of the Select tool using a mapping table. I'm currently using the Dynamic Rename and Dynamic Select tools, but the mapping isn't ideal. Here are some issues and restrictions:

1. Using only Dynamic Rename tool results in incorrect renaming, like A_2.
2. Everything needs to be dynamic so that entering a table name will produce the correct table content.
3. I tried using the Cross Tab tool, but it changes parentheses () to underscores _.

How can I resolve these issues?

[OTrieger]

 Hell @Zona 
I guess what you will need to ensure is that all source files will have the same names for the required fields. I'm not sure what are the fields names for each source, but if your will create a mapping file:
Standard Name      Source1   Source2    Source3            Description
A                             Bname      Name        First Name         Need
B                              Sex            S               Gender             Need

Each source connect to a renaming tool, replace the original naming to the Standard name. Union all the source fields. Then add additional Renaming tool, updating the Metadata of the fields. Add a Dynamic Select, Select Formula, from fields select Description and then set the formula to: 
Description="Need"
This will give you the desired output.

[Zona]

Hi @OTrieger, Thanks you so much for the reply Do you mean that I need to add an extra column "Description" in the below table stating if the column is needed? I don't have to rename every column. For instace, table1 just have to select out column (B),C,D from the original table. I just want to use the Dynamic Rename to pick out the columns I need. Only table4 requires to rename the column.

[OTrieger]

Hi @Zona 

I'm not sure how many fields you have in the different source files, adding the Description column will ensure that you will end up only with the needed fields no matter how many fields are in the files

[apathetichell]

My usual way to do this is:

input 1) source data.

input 2) mapping data. this is a map of current column name ot desired column name. feel free to have another column for table type here.

control parameter one - this feeds in a list of columns I need to remap.

control parameter two - this feeds in the file I am looking for.

Control parameter one receives from outerworkflow the (filtered by desired final output file type) list of fields you want in a concatenated format list ie "field1","field2","field3",etc...

control parameter one -> action tool -> dynamic select - dynamic select looks or [Name] = "RecordID" or [Name] in("Test") - the action tool replaces the specific string "Test" - because I have included the ""s in what I'm sending in via control parameter.

control parameter two is the match for the fields/table type. This action tool leads to a filter tool which is connected to my mapping input. It filters for the table/rename/current field (and probably position of the field). after filtering this connects to a dynamic rename. If I need to sort and order is very important - I will often change the fliename to a number first. use a select tool to sort by that number - and then add a seocnd dynamic rename to change to the actual fieldname I need.

I would strongly recommend NOT hooking up an action tool to dynamic rename. I would strongly recommend NOT using a text box for fieldname.

[Zona]

@apathetichell Hi, thanks for your reply. I'm not sure how control parameter work in this case, Can you please show me on a workflow?

[apathetichell · Accepted solution]

See attached. Use the filter in the outerworkflow to control which table you are trying to create.

[Zona]

@apathetichell Thanks a lot!!! It worked well. However there are a few questions I want to clarify:

1. Why do we need the RecordID tool in the inner macro?

2.  How to rename table 5 after control parameter 2 ?

[Zona]

@apathetichell If you have time, can you please show me how to sort the fields by renaming them with number? I'm stucked here.

[apathetichell]

Taking a step back - what are you doing? Are you looking to map a new name to a column by position or are you selecting columns based upon name? Your grid does not fit both of those two activities.

Activity 1) table 2 is the original columns A,B,C. They have new names. (B),C,D

Activity 2) table 2 consists of the original columns (B),C,D

The problem is let's assume Activity 2) is correct... what are you doing with table 4,table 5? This takes positional arguments whereas the first 3 took name arguments? this makes no sense. your workflow should handle one of the two formats - not both or have a logic to dictate which type of table it is..  The one I build is for type 2) and should work for type 2. I went with type 2 because it was the only one which logically worked for table 2 and table 4. 

A standard numeric rename strategy is to use field info. recordid, dynamic rename - where you match the column name to name - and then use recordid as the new name.