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?

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?

Reporting

Accepted answers

danilang

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

DynamicSelectColumns.yxmd

All comments

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.

SelectedColumns-GS.yxmd

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?

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167