Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
viswas1986
New Contributor III

Comparing two Excel files

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 FileNew File
CAT 9CAT5
CAT5CAT4
CAT4CAT3
CAT3CAT2
CAT1CAT0

 

Negative Change
Old FileNew File
CAT5CAT 9
CAT4CAT5
CAT3CAT4
CAT2CAT3
CAT0CAT1

 

Jump
Old FileNew File
CAT5CAT 3
CAT4CAT2
CAT3CAT1
CAT2CAT0
CAT0CAT9

Thank you

18 Replies
MVP
MVP

Re: Comparing two Excel files

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

Re: Comparing two Excel files

Can you post some examples sources files?


talk is cheap, supply exceeds demand
viswas1986
New Contributor III

Re: Comparing two Excel files

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

Re: Comparing two Excel files

Can you post some examples sources files?


talk is cheap, supply exceeds demand
viswas1986
New Contributor III

Re: Comparing two Excel files

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

Re: Comparing two Excel files

Last chance. Can you post some examples sources files?


talk is cheap, supply exceeds demand
shubham_singh
Contributor II

Re: Comparing two Excel files

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
New Contributor III

Re: Comparing two Excel files

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
Contributor II

Re: Comparing two Excel files

//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;

Community Browser