Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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

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

Error.JPG

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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.

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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.

Highlighted
Contributor III
Contributor III

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]

Highlighted
Contributor III
Contributor III

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],

Highlighted
Partner
Partner

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.