topic Re: Expression help in QlikView

Expression help

hopkinsc — Thu, 25 Oct 2012 12:48:31 GMT

Hi,

I am trying to create a pivot table showing job details. I don't want to show any jobs with a JobFinishDate of xx/xx/xxxx 23:59:59 (X being the date)

here is an example..

Does anyone know how i can hide this value please?

I was thinking of making the JobFinishDate an expression and doing an IF statement but i want it to ignore the date part of the timestamp and only exclude values matching 23:59:59 on the time part.

Thanks

Re: Expression help

MayilVahanan — Thu, 25 Oct 2012 13:51:37 GMT

Try like this

load *,if(text(time(JobFinishDate))='23:59:59',1,0) as RegionFlag Inline

[

CompletedRegion,RegionSales,JobFinishDate

Central,1000,'23/12/2011 12:11:45'

Central,1000,'23/12/2011 23:59:59'

];

Then in expression, use like this

=Sum({<RegionFlag = {0}>}RegionSales)

From that, we can exclude the xx/xx/xxxx 23:59:59 in table.

Hope it helps

Regards,

R.MayilVahanan

Re: Expression help

hopkinsc — Thu, 25 Oct 2012 14:03:39 GMT

Hi, i want to hide ALL JobFinishDates with a time of 23:59:59 not just the 2 listed.

I thought i could have used a function that could ignore the first 11 characters (The date) and then compare the remaining characters. and if the remaining characters = 23:59:59 then not show it.

Re: Expression help

MayilVahanan — Thu, 25 Oct 2012 14:08:36 GMT

Just load a sample for your requirement, it suit for all JobFinishDates with a time of 23:59:59.. If you set flag in script means, performance also high ..

Hope it helps..