From a given list of column headers, check if the other files have those headers or not.

[ShounakGhosh]

I have 4 transaction files, and I have another file with the list of Column Headers.

I just need to check if all the Column headers that are mentioned in the file, exist in the Transaction Files. Like a check list.

I was thinking of creating a report or just updating the current Column Header file with the updated data of which files the headers are missing out from.

How do I approach this? 

Thank You for your help 🙂

[atcodedog05]

Hi @ShounakGhosh 

You can use field info tool to get column header and then you can use find & replace tool to do vlookup to match with the original column list.

https://help.alteryx.com/20212/designer/field-info-tool

If you can provide sample data files we can help you with an example.

Hope this helps : )

[ShounakGhosh]

thank you, I am going thru the Field info Tool.

These are the column names:

Column Names

A

B

C

D

E

F

G

H

Sample Transaction Files

1. 

A	B	C	D	E	F	G
1	0	1	1	0	0	1
1	0	1	1	1	0	1
0	1	1	0	0	0	1
1	1	0	0	1	0	0
1	1	0	0	0	1	0

File 2:

A	B	D	E	G
1	0	1	1	0
1	0	1	1	1
0	1	1	0	0
1	1	0	0	1
1	1	0	0	0

File 3:

B	D	E	F	H
1	0	1	1	0
1	0	1	1	1
0	1	1	0	0
1	1	0	0	1
1	1	0	0	0

So basically as you can see for example, Column Name: H doesnt exist in File 1 and 2, so in the report or the result it generated as The list of files that doesnt have H as a Column Header. 

[Jean-Balteryx]

You want to generate a report for each header telling which file doesn't contain it ?

[ShounakGhosh]

Report is just a preference, but it can be produced in just another file stating file names that the columns are missing. 

Like 

Column Name	File Name
H	File 2, File 3

Thank you.

[Jean-Balteryx]

Can you provide sample files as Excel or csv ?

[atcodedog05 · Accepted solution]

Hi @ShounakGhosh 

You can do something like this.

Workflow:

1. Use field info to get column names of files.

2. Use formula tool to tag the columns to their file name.

3. Using multi-join tool to join on column names.

4. Using formula tool to create consolidated list of file names.

5. Using select tool to keep only required column.

Hope this helps : )

[ShounakGhosh]

Just made some sample files. 

[atcodedog05]

Hi @ShounakGhosh

You can check the above solution and let me if that works for you 🙂.

[ShounakGhosh]

Thank you, it definitely runs.

Im trying to use the same to read transaction files from a directory now.