topic Re: Which date comes later in QlikView

Which date comes later

gfisch13 — Wed, 25 Nov 2020 16:16:04 GMT

Hi - I have a policy master file that has three dates, an issue date, a lapse date and a new issue date. I want to be able to identify any policies that we wrote after the LapseDate. I was thinking of doing this with a list box, but creating an indicator to use in a field I add to the table would work too, I'm not certain on how to pull this off.

I tried a variation of NewIssueDate>=LapseDate as an expression for a list box, but it didn't seem to calc properly.

Ex.....

IssueDate	LapseDate	NewIssueDate	Ind
01/01/2016	09/01/2016		N
03/05/2016			N
05/01/2016	06/01/2016	09/01/2016	Y
05/02/2016	06/30/2016	07/10/2016	Y

Any help is appreciated!

George

Re: Which date comes later

sunny_talwar — Mon, 25 Sep 2017 18:29:25 GMT

May be this

If(If(Len(Trim(NewIssueDate)) = 0, 0, NewIssueDate) >= If(Len(Trim(LapseDate)) = 0, 1, LapseDate), 'N', 'Y') as Ind

This will create your flag... not sure what do you want to see in the list box? The flag or something else?

Re: Which date comes later

gfisch13 — Mon, 25 Sep 2017 18:34:10 GMT

I would like to the the flag in the list box, so the Y and N.

Re: Which date comes later

sunny_talwar — Mon, 25 Sep 2017 18:34:44 GMT

So, once you create the above in the script... you should be able to use Ind as your list box

Re: Which date comes later

gfisch13 — Mon, 25 Sep 2017 18:38:50 GMT

Can this expression be used to setup the listbox or is it better to load it in the script and be a part of the table object first?

Re: Which date comes later

sunny_talwar — Mon, 25 Sep 2017 18:40:09 GMT

I would suggest doing this in the load script as it avoids unnecessary complexities on the front end of your application.... if you still want this to be on the front end... you can try this as your list box expression

If(If(Len(Trim(NewIssueDate)) = 0, 0, NewIssueDate) >= If(Len(Trim(LapseDate)) = 0, 1, LapseDate), 'N', 'Y')

Re: Which date comes later

gfisch13 — Mon, 25 Sep 2017 19:55:41 GMT

Really appreciate your input, as always!!!