Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks for your immediate response. I tried but encountered an error. Could you please help me on this.
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
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.
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
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.
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]
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],
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.