Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Re: Comparing two Excel files

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

Re: Comparing two Excel files

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

Error.JPG

viswas1986
New Contributor III

Re: Comparing two Excel files

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

Re: Comparing two Excel files

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

Re: Comparing two Excel files

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

Re: Comparing two Excel files

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

Re: Comparing two Excel files

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

Re: Comparing two Excel files

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

Re: Comparing two Excel files

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.

Community Browser