In Script select a date filtered on another date field

[#1]

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

[sunny_talwar]

So you have Headcount table and you are trying to create TempTable?

[#3]

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

[sunny_talwar · Accepted solution]

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;

[#5]

This works great! Thanks again Sunny!