Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

select columns that match between two files

MGT
6 - Meteoroid

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?

7 REPLIES 7
grazitti_sapna
17 - Castor

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

grazitti_sapna_1-1613625325438.png

 

2. File 2 contains 2 columns.

grazitti_sapna_2-1613625345384.png

 

3. Final Output

 

grazitti_sapna_0-1613625284104.png

 

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

Sapna Gupta
MGT
6 - Meteoroid

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
17 - Castor

@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?

 

grazitti_sapna_1-1613626746692.png

 

 

grazitti_sapna_0-1613626734764.png

 

Sapna Gupta
MGT
6 - Meteoroid

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
17 - Castor

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?

Sapna Gupta
danilang
19 - Altair
19 - Altair

Hi @MGT 

 

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

 

w.png

 

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
6 - Meteoroid

Thank you! A nice clean solution

Labels