1 Reply Latest reply: Sep 10, 2018 12:21 PM by Quy Nguyen RSS

    Expired license count for selected Period

    Girish Kumar

      Hi All,

       

      I have license data of an inspector in which Inspector_ID, Activation_date, Expiry_Date and other fields. Once the license is expired, they may issue new license for the same inspector. So the Inspector_IDs are not unique. How do i get to know the number of inspectors whose license is expired for selected period in qlik sense text object.

       

      Eg:

       

      ID  ActDate    ExpDate

      1   1 Jan 18    3 Mar 18

      2   2 Jan 18   10 Mar 18

      3   4 Apr 18   10 Dec 18

      1   4 Mar 18    12 Dec 18

       

      if the selected period is Jun 2018, Result should be 1 (ID = 2)

      Please do help asap.

       

      Thanks,

      Girish.

        • Re: Expired license count for selected Period
          Quy Nguyen

          It will be a little bit complex. The ideal is create the date field matches with every date range from your data.

          Here is the code. I also attached the app in case you want to see the script and some expressions to show the active/expired account. !

          Temp:
          Load * INLINE [
          ID, ActDate , ExpDate
          1 ,1 Jan 18,3 Mar 18
          2 ,2 Jan 18,10 Mar 18
          3 ,4 Apr 18,10 Dec 18
          1 ,4 Mar 18,12 Dec 18
          4 ,4 July 18,12 Dec 18
          ];
          
          
          //Format data and add flag
          NoConcatenate
          Data:
          Load *,
           1 As ActiveFlag,
           ActDate&'_'&ExpDate As KeyRange;
          Load ID, 
           Date(Date#( ActDate, 'D MMM YY')) As ActDate,
               Date(Date#( ExpDate, 'D MMM YY')) As ExpDate
          Resident Temp;
          Drop Table Temp;
          
          
          // Loop for creating date through date range
          TempCal:
          Load Date,
           MonthName(Date) As MonthName;
          Load 
           Date(StartDate + IterNo() -1 ) AS Date While (StartDate + IterNo() - 1) <= Num(EndDate);
          Load Min(ActDate) As StartDate,
           Max(ExpDate) As EndDate
          Resident Data;
          // Match date with range
          Inner Join
          IntervalMatch(Date)
          Load
           ActDate, ExpDate
          Resident Data;
          
          
          //Final Calendar
          Cal:
          Load 
           Date,
              MonthName,
              ActDate&'_'&ExpDate As KeyRange
          Resident TempCal;
          Drop Table TempCal;