topic Re: Invalid expression in QlikView

Invalid expression

Thu, 25 Sep 2014 21:11:21 GMT

hello.

i have the following invalid expression and cannot understand why it is throwing error.

any experts who can perhaps point me in the right direction?

Invalid expression

KALKYL:

LOAD

DISTINCT %Key,

if(isNull(TILLTRADE_TOM),

DATE(MAX(PREVIOUS(TILLTRADE_FOM),

DATE(MAX(TILLTRADE_FOM))))) AS TEST

RESIDENT TEMP1

GROUP BY %Key

Best

Brad

Re: Invalid expression

maxgro — Thu, 25 Sep 2014 21:35:11 GMT

from the syntax side, you have a group by, so you need an aggr function

like this

max(if(isNull(d), DATE((PREVIOUS(d))), DATE((d)))) AS TEST

don't know if it makes sense

Re: Invalid expression

Thu, 25 Sep 2014 21:59:59 GMT

Hi,

If possible could you attach a sample source for that script??

Re: Invalid expression

hewemel1 — Fri, 26 Sep 2014 16:51:19 GMT

I guess you would like to get the following:

Group the data by %Key. For each group, get the max of field TILLTRADE_FOM and return it as a date. But in case all the data of a group only has NULLs in the field, get the max of the previous group.

If my assumption is correct you could do it as follows:

First, perform the group by and calculate the max(TILLTRADE_FOM) and create a new table with this.

In a second step, read in this table and check, where you got NULL as max and replace it by the result of the previous row (group of rows in your original data).

Otherwise: Could you describe what you would like to get? Could you provide sample data?