Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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!