topic Re: Expression help in QlikView

Expression help

danielnevitt — Fri, 16 May 2014 15:54:53 GMT

Hi,

I am trying to write an expression to how where payments are overdue:

sum({<pay_due_dt={"<$(=vToday)"},pay_made_dt={'-'}>}extended_amt)

However the above does not work, as the '-' does not seem to be recognised. Is there another was of writing the expression to show where the field is blank or - or Null etc?

Thanks for any help.

Regards,

Daniel

Re: Expression help

SunilChauhan — Fri, 16 May 2014 15:58:17 GMT

sum({<pay_due_dt={"<$(=vToday)"},pay_made_dt={' '}>}extended_amt)

sum({<pay_due_dt={"<$(=vToday)"},pay_made_dt={$(=null()))}>}extended_amt)

hope this helps

Re: Expression help

Anonymous — Fri, 16 May 2014 16:03:44 GMT

Or maybe this:

sum({<pay_due_dt={"<$(=vToday)"}> - <pay_made_dt={*}>} extended_amt)

Re: Expression help

danielnevitt — Fri, 16 May 2014 16:05:29 GMT

Hi Sunil,

Thank you for the reply. Unfortunately neither of those suggestions work.

If I include WHERE pay_made_dt IS NULL in the SQL load, it removes all items without a pay_made_dt.

However I have other tables which need the pay_made_dt, so I can't remove at the data load point.

Thanks,

Daniel

Re: Expression help

SunilChauhan — Fri, 16 May 2014 16:34:44 GMT

use lenth function in where

WHERE len(pay_made_dt)=0 we are this in qlikview

'check for lenth function in sql and use in place of len here

you can use this

WHERE pay_made_dt =' '

hope this helps