topic Re: In Script select a date filtered on another date field in QlikView

In Script select a date filtered on another date field

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: In Script select a date filtered on another date field

sunny_talwar — Mon, 24 Oct 2016 16:57:15 GMT

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

Re: In Script select a date filtered on another date field

Tue, 25 Oct 2016 09:17:23 GMT

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!

Re: In Script select a date filtered on another date field

sunny_talwar — Tue, 25 Oct 2016 23:14:43 GMT

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;

Re: In Script select a date filtered on another date field

Wed, 26 Oct 2016 09:16:16 GMT

This works great! Thanks again Sunny!