Using Lookup files and identifying the changes in each file

[madakkas]

Hi All,

I have a question that I am trying to solve …

I have two files which I can upload to be used as inputlookup.csv

Sample as below
tab1.csv
Date Col1 Col2 Col3 Col4 Col5
1-Jan-18 Y N N N N
2-Jan-18 Y N N N N
3-Jan-18 Y Y Y Y Y
4-Jan-18 Y Y Y Y Y
5-Jan-18 Y Y Y Y Y
6-Jan-18 Y Y Y Y Y
7-Jan-18 Y N N N Y
8-Jan-18 Y N N N Y
9-Jan-18 Y Y N Y Y
10-Jan-18 Y Y Y Y Y

tab2.csv

Date Col1 Col2 Col3 Col4 Col5
1-Jan-18 Y N N N N
2-Jan-18 Y N N N N
3-Jan-18 Y Y Y Y Y
4-Jan-18 N Y Y Y Y
5-Jan-18 Y Y Y Y Y
6-Jan-18 Y Y Y Y Y
7-Jan-18 Y N N N Y
8-Jan-18 Y N N N Y
9-Jan-18 Y Y Y Y Y
10-Jan-18 Y Y Y Y Y

I am looking for a way to join these two tables and sort the areas where there is a change as below

Date Col1 Col2 Col3 Col4 Col5 Output
4-Jan-18 N

9-Jan-18 Y

|inputlookup tab1.csv
|lookup tab2.csv Date Col1 Col2.

this gives me a complete set of results along with the mismatched columns. How can i remove all except the mismatched columns.

thank You in advance.

[adonio · Accepted solution]

@madakkas,
copied your sample to the letter. here is a screenshot.
can you double check

[madakkas]

Sorry mate , I had the dates in two different formats and hence the whole differences where thrown up . It did get the work moving, but is there a way we can identify the column which had the change ?

[adonio]

i am puzzled by it as well. trying to come up with a solution and will post it here once i figure it out

[madakkas]

I did something as below , though it is a bit complicated. Will as well wait for your advise if you have any simpler thoughts.

Kind of created a macro as below

|inputlookup tab1.csv| append [inputlookup tab2.csv]
|table Date $col_num$
|chart count over Date by $col_num$ |eval col_name = "$col_num$" |where N = 1 or Y = 1
|table Date col_name

and then will have to do an append to call the macro multiple times with the number of columns available.

[adonio]

try this out as a base maybe we can work form there:

 | inputlookup tab2.csv
    | search NOT [| inputlookup tab1.csv ]

this will give you the entire line in which there was a change
hope it helps a little

[madakkas]

it just gave me the entire tab2.csv, not the differences in them .