topic Re: Set Expression Problem in QlikView

Set Expression Problem

tamilarasu — Thu, 25 Jun 2015 08:58:35 GMT

Hi,

I am trying to get count of prefixes based on few criteria. I have tried the below expression and getting the desired result but additional unwanted lines.

P.DBI={">$(=Date(Yearend(Today(),5),'YYYY-MM-DD'))"} - Here the criteria is .BPI field should greater than 5 years from today (2015-12-31)

P.DBI-={'9999*'} - I just exclude the date which starts with 9999.

Count(DISTINCT {1}{<P.KR={'1','2'},P.KSL={'ZD'},P.DBI={">$(=Date(Yearend(Today(),5),'YYYY-MM-DD'))"}, P.DBI-={'9999*'}>

<P.KR={'1','2'},P.KSL={'ZD'},P.DAB-={'20*','19*','199*'}>}M.PREFIX)

But I don't know why I am getting results in the year 2015. I have tried to the same code by excluding the condition P.DBI-={'9999*'} and the result is fine but when I add this P.DBI-={'9999*'}, getting extra line.

What could be the reason.?

Re: Set Expression Problem

swuehl — Thu, 25 Jun 2015 09:30:05 GMT

I believe you can't modify the same field twice in a single set modifier, ie. assigning two element lists to P.DBI in your first set modifier. I believe QV will take just the second one.

If you want to take 9999 out, try

Count(DISTINCT {1}{<P.KR={'1','2'},P.KSL={'ZD'},P.DBI={">$(=Date(Yearend(Today(),5),'YYYY-MM-DD'))<=$(=Date(Year(9999)))"} >

<P.KR={'1','2'},P.KSL={'ZD'},P.DAB-={'20*','19*','199*'}>}M.PREFIX)

Re: Set Expression Problem

tamilarasu — Thu, 25 Jun 2015 10:06:17 GMT

Thank you for your reply. If I use the above expression, the expression $(=Date(Year(9999))) will return the date as 04-10-1905. I want to exclude the dates end with 9999.

Re: Set Expression Problem

swuehl — Thu, 25 Jun 2015 10:22:38 GMT

Right, my mistake.

$(=Date(MakeDate(9999),'YYYY-MM-DD'))

but I think even

$(=MakeDate(9999)) should be enough within your advanced search.

Re: Set Expression Problem

tamilarasu — Fri, 26 Jun 2015 10:07:57 GMT

Swuehl,

If we use above expression, the date will be 999-01-01. I have changed the code to $(=Date(MakeDate(9999,12,31),'YYYY-MM-DD')) and its working fine. If possible edit the code which I am going to mark it as correct. It might be useful to someone in future. Thanks a lot for your help.

and cool weekend!!