From a given list of column headers, check if the other files have those headers or not.
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 🙂
Solved! Go to Solution.
- Labels:
- Data Investigation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 : )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You want to generate a report for each header telling which file doesn't contain it ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you provide sample files as Excel or csv ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 : )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Just made some sample files.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you, it definitely runs.
Im trying to use the same to read transaction files from a directory now.
