select columns that match between two files

[MGT]

I have a file with 150 columns and I only want to select 50 of them. 

• File one has all the data, 150 columns and 500k records
• File two has only the names of the columns for the report I need to create.

How can I create a report  with the columns in file 2 with the data contained in file 1?

[grazitti_sapna]

Hi @MGT , If I understand your concern correctly what you can try is that first you can transpose the file with data and columns and join the required columns with the other file if they are same then you can cross tab the data which will result in the data for the selected column.

1. My input contains 4 fields

2. File 2 contains 2 columns.

3. Final Output

If this is what you are looking for kindly mark this post as solution.

[MGT]

the first transpose function is only returning two columns. Can see this is not the case in your example. Could it be because of the size?

[grazitti_sapna]

@MGT , This is my transpose configuration and output. Did you try using a browse tool? Without browse tool the default data display size will be 1MB.

If possible could you possibly share your sample dataset?

[MGT]

I added the Record ID function and the Transpose function worked. However, the join is giving the result "conditional" for all names, values are empty and columns has also "conditional" as result.  The second Transpose function has only one record.

[grazitti_sapna]

Does the column names in first file match with the other file? The column names must be same in both the files. Could you share your workflow in order to see what is the issue?

[danilang · Accepted solution]

Hi @MGT 

As an alternative to @grazitti_sapna's solution, you can try one that only acts on the table meta-data.  

Once you build the new column names in the formula tool, use a Dynamic Rename in Take field names from right input rows mode to add "keep__" to the columns that are specified in the "Columns to keep" file.  Use a Dynamic Select to select the columns that start with "keep__" and then use a second Dynamic Rename in Formula mode to remove the "keep_" from the column names.

By acting on the meta data only, the workflow runs much faster and avoids the issues that can arise from using a transpose/cross tab tool pair, such as column reordering and having special characters replaced in the final column names

Dan

[MGT]

Thank you! A nice clean solution