Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;