18 Replies Latest reply: Dec 27, 2016 1:28 AM by Shubham Singh RSS

    Comparing two Excel files

    Viswanathan Srinivasan

      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

        • Re: Comparing two Excel files
          Jonathan Dienst

          How do you associate the values and lines in the "old" file with the values and lines in the "new" file?

          • Re: Comparing two Excel files
            Gysbert Wassenaar

            Can you post some examples sources files?

              • Re: Comparing two Excel files
                Viswanathan Srinivasan

                If there is CAT9 is specified in the Old file and CAT 0 is updated in the new file, it is positive change. The sequence is like

                 

                Old File      New File      SET ANALYSIS RESULT SHOULD BE

                CAT9           CAT 0                   Positive change

                CAT 0          CAT 9                   Negative change

                CAT 1          CAT 9                   Jump

                 

                First the project which starts will be in CAT 9

                Then feasibility check, if it is found feasible and can be done by us, then it will go to CAT 5

                Tooling and Fixture checking will happen in CAT 4

                Prototype will be produced in CAT 3

                Fitment trail will be carried out in CAT 2

                Customer Approval like PPAP will be done in CAT 1

                If project is Successful, it will go to CAT 0

                If project is not successful, it will go to CAT 9 once again.

                 

                This is the process. Actually all these i am doing in excel sheet. I have daily track on it. If there is any change on this category i want to find it with any SET Analysis.

                 

                Hope this will give you a clear picture.

                 

                Thanks for your immediate response.

                 

                Viswanathan

                  • Re: Comparing two Excel files
                    Gysbert Wassenaar

                    Can you post some examples sources files?

                      • Re: Comparing two Excel files
                        Viswanathan Srinivasan

                        Old excel file (01.12.2016)                        New Excel file (23.12.2016)                 Result when comparing this 2

                        CAT 9                                                     CAT 5                                                Positive Change

                        CAT 5                                                     CAT 0                                                Positive Change

                        CAT 4                                                     CAT 3                                                Negative Change

                        CAT 3                                                     CAT 1                                                Jump

                          • Re: Comparing two Excel files
                            Gysbert Wassenaar

                            Last chance. Can you post some examples sources files?

                              • Re: Comparing two Excel files
                                Viswanathan Srinivasan

                                gwassenaar

                                Sorry Gysbert, I have not clearly understood what do you require to answer me. This is the reason why I have blabbered something previously.

                                 

                                Hope you want me to upload the 2 files with which I am working. I am uploading it. If I have not fulfilled your expectation, kindly advise me.

                                 

                                You can find two fields in the files. Tracking and Category. Here Tracking is my key and with respect to it, I want to check the change in Category between the two files. Mainly if the Category changes to CAT 0 from any other previously specified, I need to track it. I will also share a syntax which I tried, hope this will help you in understanding better.

                                 

                                if(("Category Old"="CAT 9","CAT 5","CAT 1") and ("Category New"="CAT 0"),'CAT 0 is updated','No CAT 0 is Updated)

                                 

                                Thanks for your continuous support.

                                  • Re: Comparing two Excel files
                                    Shubham Singh

                                    //Use this code

                                     

                                    //All all the possible combinations of Category change here

                                    ChangeMap:

                                    Mapping LOAD * INLINE [

                                    ChangeType,     ChangeName

                                    CAT 9|CAT 5,         Positive Change

                                    CAT 5|CAT 4,         Positive Change

                                    CAT 4|CAT 3,         Positive Change

                                    CAT 3|CAT 2,         Positive Change

                                    CAT 1|CAT 0,         Positive Change

                                    CAT 5|CAT 9,         Negative Change

                                    CAT 4|CAT 5,         Negative Change

                                    CAT 3|CAT 4,         Negative Change

                                    CAT 2|CAT 3,         Negative Change

                                    CAT 0|CAT 1,         Negative Change

                                    CAT 5|CAT 3,         Jump

                                    CAT 4|CAT 2,         Jump

                                    CAT 3|CAT 1,         Jump

                                    CAT 2|CAT 0,         Jump

                                    CAT 0|CAT 9,         Jump

                                    CAT 9|CAT 9,         No Change

                                    CAT 5|CAT 5,         No Change

                                    CAT 5a|CAT 5a,     No Change

                                    CAT 4|CAT 4,        No Change

                                    CAT 3|CAT 3,        No Change

                                    CAT 2|CAT 2,        No Change

                                    CAT 1|CAT 1,        No Change

                                    CAT 0|CAT 0,        No Change

                                    ];

                                     

                                     

                                    Table1:

                                    LOAD

                                        "TRACKING #" as TrackingID,

                                        "Category Old"

                                    FROM [lib://Data/CAT-(01.12.2016).xlsx]

                                    (ooxml, embedded labels, table is owssvr);

                                     

                                    outer join     // you shouln't miss any tracking ID

                                     

                                    LOAD

                                        "TRACKING #" as TrackingID,

                                        "Category New"

                                    FROM [lib://Data/CAT-(20.12.2016).xlsx]

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

                                     

                                     

                                    Table2:

                                    LOAD

                                    TrackingID,

                                    "Category Old",

                                    "Category New",

                                    ApplyMap('ChangeMap',"Category Old"&'|'&"Category New",'ChangeNotDefined') as Change

                                    Resident Table1;

                                     

                                    Drop Table Table1;

                                      • Re: Comparing two Excel files
                                        Viswanathan Srinivasan

                                        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

                                          • Re: Comparing two Excel files
                                            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.

                                              • Re: Comparing two Excel files
                                                Viswanathan Srinivasan

                                                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

                                                  • Re: Comparing two Excel files
                                                    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.

                                                  • Re: Comparing two Excel files
                                                    Viswanathan Srinivasan

                                                    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.

                                                    • Re: Comparing two Excel files
                                                      Viswanathan Srinivasan

                                                      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]

                                                • Re: Comparing two Excel files
                                                  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;
                                                  
                                                  
                                                  
                                      • Re: Comparing two Excel files
                                        Shubham Singh

                                        Answering to your question without sample data would be like firing in the dark.

                                        Your problem seems easy but I can't do anything without any sample data.