1 Reply Latest reply: Jun 16, 2017 11:00 AM by Andrey Khoronenko RSS

    Date Comparison in qliksense

    Sankar Rajamoorthy

      Hi Folks,

      i have a table like below

       

      Table1:              Table2:                    Table3:

        Acc Day                Acc Day              Acc Day

          A    1                    A    2                    B    3

          B    1                    C    2                    G    3

          C    1                    F    2                    H    3

          D    1                    D    2                    A    3

          E    1                    M    2                    J    3

       

      i have to show active and Inactive account based on day .

       

      Active Acc is which is not repeated from the previous day

      (ex:Day-1 have 5 active Acc because that was the first day & Day-2 have  two active Acc (F,M) compared with previous day  & Day-3 have four active Acc (B,G,H,J) compared with previous day.


      Inactive Acc is which acc is not repeated from previous day Acc into the present account table

      (ex: Day-3 have four inactive accounts(C,F,D,M) and Day-2 have two inactive account(B,E) and Day-1 have 0 inactive)

       

      Output:

              Day                    1          2              3

      active-Inactive          5-0        2-2          4-4

       

      So

       

      i using the below code for active account:

      master:

      Load

      Acc,

      Day,

      if(Acc<>Previous(Acc),'Active') as flag

      Resident master01 Order by Acc,Day;

       

      Its working and got active accounts & am using same code with Descending order for inactive but its not working

       

      Load

      Acc,

      Day,

      if(Acc<>Previous(Acc),'Inactive') as flag

      Resident master01 Order by Acc,Day Desc;

       

       

      How to find inactive account any suggestion please..

        • Re: Date Comparison in qliksense
          Andrey Khoronenko

          Hi,

           

          May be like this (look attached file)

           

          Table1:

          Load*Inline           

          [Acc, Day           

          A, 1                 

          B, 1                   

          C, 1                 

          D, 1                 

          E, 1];               

             

          Table2:

          Load*Inline

          [Acc, Day

          A, 2

          C, 2

          F, 2

          D, 2

          M, 2

          ];

           

          Table3:

          Load*Inline

          [Acc, Day

          B, 3

          G, 3

          H, 3

          A, 3

          J, 3

          ];

           

          NoConcatenate

          Table4:

          Load

          Day,

          Concat(Acc, '') as Acc

          Resident Table1

          Group By Day;

           

          Drop Table Table1;

           

          Left Join

          Load*,

          If(RecNo()=1, Len(Acc), Len(Acc)-Len(KeepChar(Acc, Previous(Acc))))&'-'&

          If(RecNo()=1, 0, Len(PurgeChar(Acc, Previous(Acc)))) as Active_InActive

          Resident Table4;

           

          Drop Field Acc;


          Result

          1.jpg

          Regards,

          Andrey