Discussion Board for collaboration related to QlikView App Development.
Hi guys, hopefully someone can help me with this one!
I have a Headcount table of employment records with gaps. I want to identify the first Start Date following the last Leaving Date.
I have built a TempTable capturing each Person Number and their Last Leaving Date. I want to Join to this, or Lookup as I load it, the Start Date that follows the Last Leaving Date.
Something like:
Min(Start Date) Where (Start Date > Leaving Date) - to give the result in bold below.
Any suggestions? Thanks for the help!
Mike
Headcount:
Person Number | Start Date | Leaving Date |
1 | 04/07/2011 | 24/12/2013 |
1 | 29/06/2015 | |
1 | 29/07/2015 | |
2 | 04/07/2011 | |
2 | 29/06/2015 | 31/07/2015 |
2 | 01/09/2015 |
TempTable:
Person Number | Last Leaving Date | Last Start Date |
1 | 24/12/2013 | 29/06/2015 |
2 | 31/07/2015 | 01/09/2015 |
May be something like this?
Headcount:
LOAD [Person Number],
[Start Date],
[Leaving Date]
FROM
[https://community.qlik.com/thread/237456]
(html, codepage is 1252, embedded labels, table is @1);
Left Join (Headcount)
LOAD [Person Number],
[Leaving Date] as Temp
Resident Headcount
Where Len(Trim([Leaving Date])) > 0;
TempTable:
LOAD [Person Number],
[Leaving Date] as [Last Leaving Date]
Resident Headcount
Where Len(Trim([Leaving Date])) > 0;
Left Join (TempTable)
LOAD [Person Number],
Date(Min([Start Date])) as [Last Start Date]
Resident Headcount
Where [Start Date] > Temp
Group By [Person Number];
DROP Field Temp;
So you have Headcount table and you are trying to create TempTable?
Hi. Thanks for the help!
I’m trying to build TempTable.
I want to filter the Start Date field down to just one Last Start Date for each Person Number. This can go in TempTable, or a new table if necessary.
So far I have the script below.
Headcount:
LOAD ,
FROM
;
tempTable:
Load
,
Max() as ,
Min() Where ( > Max()) as // This does not work.
Resident Headcount
Where <= Today(0)
Group By ;
Thanks!
M
May be something like this?
Headcount:
LOAD [Person Number],
[Start Date],
[Leaving Date]
FROM
[https://community.qlik.com/thread/237456]
(html, codepage is 1252, embedded labels, table is @1);
Left Join (Headcount)
LOAD [Person Number],
[Leaving Date] as Temp
Resident Headcount
Where Len(Trim([Leaving Date])) > 0;
TempTable:
LOAD [Person Number],
[Leaving Date] as [Last Leaving Date]
Resident Headcount
Where Len(Trim([Leaving Date])) > 0;
Left Join (TempTable)
LOAD [Person Number],
Date(Min([Start Date])) as [Last Start Date]
Resident Headcount
Where [Start Date] > Temp
Group By [Person Number];
DROP Field Temp;
This works great! Thanks again Sunny!