Comparing two Excel files

[viswas1986]

Dear all,

I am now creating a new App in qliksence. I am having two excel files whose Header will not change any day. My main goal is to compare the two files and if there is any change Qliksence should let me know.

Now i am in to a task, in a column of old file there will be data filled like CAT0, CAT1, CAT2, CAT3, CAT4, CAT5 and CAT 9. In the new file also the same will be filled but the main thing here is, if the changes between old file and new file is coming down (for eg- in old file if it is CAT 9 and in new file it is CAT 5), qliksence should say it is positive difference if it is going up (for eg- in old file if it is CAT 5 and in new file it is CAT 9) it should say, it is Negative difference. Can anyone help me on this.

Please find the below tables which shows what are all the set analysis i need to do.

Positive Change
Old File	New File
CAT 9	CAT5
CAT5	CAT4
CAT4	CAT3
CAT3	CAT2
CAT1	CAT0

Negative Change
Old File	New File
CAT5	CAT 9
CAT4	CAT5
CAT3	CAT4
CAT2	CAT3
CAT0	CAT1

Jump
Old File	New File
CAT5	CAT 3
CAT4	CAT2
CAT3	CAT1
CAT2	CAT0
CAT0	CAT9

Thank you

[jonathandienst]

How do you associate the values and lines in the "old" file with the values and lines in the "new" file?

[Gysbert_Wassenaar]

Can you post some examples sources files?

[viswas1986]

If there is CAT9 is specified in the Old file and CAT 0 is updated in the new file, it is positive change. The sequence is like

Old File      New File      SET ANALYSIS RESULT SHOULD BE

CAT9           CAT 0                   Positive change

CAT 0          CAT 9                   Negative change

CAT 1          CAT 9                   Jump

First the project which starts will be in CAT 9

Then feasibility check, if it is found feasible and can be done by us, then it will go to CAT 5

Tooling and Fixture checking will happen in CAT 4

Prototype will be produced in CAT 3

Fitment trail will be carried out in CAT 2

Customer Approval like PPAP will be done in CAT 1

If project is Successful, it will go to CAT 0

If project is not successful, it will go to CAT 9 once again.

This is the process. Actually all these i am doing in excel sheet. I have daily track on it. If there is any change on this category i want to find it with any SET Analysis.

Hope this will give you a clear picture.

Thanks for your immediate response.

Viswanathan

[Gysbert_Wassenaar]

Can you post some examples sources files?

[viswas1986]

Old excel file (01.12.2016)                        New Excel file (23.12.2016)                 Result when comparing this 2

CAT 9                                                     CAT 5                                                Positive Change

CAT 5                                                     CAT 0                                                Positive Change

CAT 4                                                     CAT 3                                                Negative Change

CAT 3                                                     CAT 1                                                Jump

[Gysbert_Wassenaar]

Last chance. Can you post some examples sources files?

[shubham_singh]

Answering to your question without sample data would be like firing in the dark.

Your problem seems easy but I can't do anything without any sample data.

[viswas1986]

gwassenaar‌

Sorry Gysbert, I have not clearly understood what do you require to answer me. This is the reason why I have blabbered something previously.

Hope you want me to upload the 2 files with which I am working. I am uploading it. If I have not fulfilled your expectation, kindly advise me.

You can find two fields in the files. Tracking and Category. Here Tracking is my key and with respect to it, I want to check the change in Category between the two files. Mainly if the Category changes to CAT 0 from any other previously specified, I need to track it. I will also share a syntax which I tried, hope this will help you in understanding better.

if(("Category Old"="CAT 9","CAT 5","CAT 1") and ("Category New"="CAT 0"),'CAT 0 is updated','No CAT 0 is Updated)

Thanks for your continuous support.

[shubham_singh]

//Use this code

//All all the possible combinations of Category change here

ChangeMap:

Mapping LOAD * INLINE [

ChangeType,     ChangeName

CAT 9|CAT 5,         Positive Change

CAT 5|CAT 4,         Positive Change

CAT 4|CAT 3,         Positive Change

CAT 3|CAT 2,         Positive Change

CAT 1|CAT 0,         Positive Change

CAT 5|CAT 9,         Negative Change

CAT 4|CAT 5,         Negative Change

CAT 3|CAT 4,         Negative Change

CAT 2|CAT 3,         Negative Change

CAT 0|CAT 1,         Negative Change

CAT 5|CAT 3,         Jump

CAT 4|CAT 2,         Jump

CAT 3|CAT 1,         Jump

CAT 2|CAT 0,         Jump

CAT 0|CAT 9,         Jump

CAT 9|CAT 9,         No Change

CAT 5|CAT 5,         No Change

CAT 5a|CAT 5a,     No Change

CAT 4|CAT 4,        No Change

CAT 3|CAT 3,        No Change

CAT 2|CAT 2,        No Change

CAT 1|CAT 1,        No Change

CAT 0|CAT 0,        No Change

];

Table1:

LOAD

    "TRACKING #" as TrackingID,

    "Category Old"

FROM [lib://Data/CAT-(01.12.2016).xlsx]

(ooxml, embedded labels, table is owssvr);

outer join     // you shouln't miss any tracking ID

LOAD

    "TRACKING #" as TrackingID,

    "Category New"

FROM [lib://Data/CAT-(20.12.2016).xlsx]

(ooxml, embedded labels, table is [owssvr (1)]);

Table2:

LOAD

TrackingID,

"Category Old",

"Category New",

ApplyMap('ChangeMap',"Category Old"&'|'&"Category New",'ChangeNotDefined') as Change

Resident Table1;

Drop Table Table1;