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

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

[viswas1986]

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

[viswas1986]

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]

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]

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]

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]

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]

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]

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.