Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
viswas1986
Contributor III
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
Gysbert_Wassenaar

Try this script:

mapCat:

MAPPING LOAD * INLINE [

Cat, Order

CAT 0, 1

CAT 1, 2

CAT 2, 3

CAT 3, 4

CAT 4, 5

CAT 5, 6

CAT 5a, 7

CAT 9, 8

];

Temp:

LOAD

  [TRACKING #],

      [Category Old],

      ApplyMap('mapCat', [Category Old], 'Missing in Map') as OrderOld

FROM

  [LIB://MyExcelFiles/CAT-(01.12.2016).xlsx]

  (ooxml, embedded labels, table is owssvr)

  ;

JOIN

LOAD

  [TRACKING #],

      [Category New],

      ApplyMap('mapCat', [Category New], 'Missing in Map') as OrderNew

FROM

  [LIB://MyExcelFiles/CAT-(20.12.2016).xlsx]

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

  ;

Result:

LOAD

  *,

  If(OrderOld=OrderNew, 'No change',

  If(OrderOld>OrderNew, 'Negative change',

  If(OrderNew-OrderOld=1, 'Positive change',

  If(OrderNew-OrderOld>1, 'Jump')))) as Change

RESIDENT

  Temp;

DROP TABLE Temp;


talk is cheap, supply exceeds demand
viswas1986
Contributor III
Contributor III
Author

Thanks for your immediate response. I tried but encountered an error. Could you please help me on this.

Error.JPG

viswas1986
Contributor III
Contributor III
Author

Thank you so much for your immediate response. My problem is solved .

I am having one more issue, Hope you will definitely know that Qliksence will consider the null values in two files as Different. Is there any way to handle it.

Thank you

Viswanathan

shubham_singh
Partner - Creator II
Partner - Creator II

It should give "Change not defined" if any of the value is null, again give some sample data for same if you desire something else.

viswas1986
Contributor III
Contributor III
Author

Hi Shubham Singh,

Thank you once again for helping me. Actually there will not be any null values in both. As you said, If any one value is null, then "Change not defined" is coming but  If in any situation if both the value is null, and the answer i should get as "No Change", what should i do. Kindly help me in this regards.


Thank you


Viswanathan

viswas1986
Contributor III
Contributor III
Author

I am also trying to track only CAT 0 in another sheet. With the help of your Apply map, I tried to do it but I am unable to do.

I want to track How much CAT 0 is updated at the latest file. (Comparing to Old file, how much CAT 0 is updated in NEW)

a. For eg - If there is 10 CAT 0s is in Old file and 15 CAT 0s in New file. Latest CAT 0 update is 5. This result i want it in KPI and in a Filter. and

b. If any CAT 0 is deleted in old or new file and CAT 0 is changed to any other (CAT 1, CAT 2....), i want to track it.

Could you please advise me on this regards.

viswas1986
Contributor III
Contributor III
Author

I tried the below syntax to track new CAT 0 but it didn't worked.

if("Category Old"<>"CAT 0",if("Category New"="CAT 0",'New CAT 0 Updated','No CAT0 Update')) as [CAT 0 Status]

viswas1986
Contributor III
Contributor III
Author

Also Tried,

if("PE : DISPLACEMENT CAT OLD"<>"CAT 0" and "PE : DISPLACEMENT CAT"="CAT 0",'New CAT 0 Updated','No CAT0 Update') as [CAT 0 Status],

shubham_singh
Partner - Creator II
Partner - Creator II

Add a row in mapping load like this

ChangeMap:

Mapping LOAD * INLINE [

ChangeType,     ChangeName

CAT 9|CAT 5,         Positive Change

.

.

.

.

CAT 0|CAT 0,        No Change

|,        No Change

];

If you do Old & '|' & New and both "Old" and "New" are null you will just get a pipe symbol in output, which you can map with |,No Change in mapping load.

And for tracking CAT 0, Create another mapping table like this

Mapping LOAD * INLINE [

CAT 0|CAT 1, Zero to One

CAT 0|CAT 2, Zero to Two

.

.

];

And create another applymap in final map

OR you could write those maps in your existing map.

And mark the correct answer if your original problem is solved.