Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

In Script select a date filtered on another date field

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 NumberStart DateLeaving Date
104/07/201124/12/2013
129/06/2015
129/07/2015
204/07/2011
229/06/201531/07/2015
201/09/2015

TempTable:

Person NumberLast Leaving Date
Last Start Date
124/12/201329/06/2015
231/07/201501/09/2015
1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

4 Replies
sunny_talwar

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

Not applicable
Author

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

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;

Capture.PNG

Not applicable
Author

This works great! Thanks again Sunny!