topic Re: Invalid Expression in QlikView

Invalid Expression

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

hi All,

Could someone help me out with this script?

Table1:

LOAD Distinct JobId,

IF([Distribution Upgrade]=1, num(Max(Date))-num(Today()) ) as [Days Left] //type: integer

Resident Table

Group By JobId;

the problematic line is the one marked in bold,

i know that becuase when commenting it, it runs fine,

thanks in advance,

Nir Katz

Re: Invalid Expression

vinieme12 — Sun, 03 Jul 2016 09:06:43 GMT

Instead of Today(), num(now(),0). And please recheck if [distribution upgrade] is also an Int field not char also validate your date field just incase.

Re: Invalid Expression

lironbaram — Sun, 03 Jul 2016 09:13:48 GMT

it might be because of the [Distribution Upgrade] if a field isn't part of the group by

you need to apply aggregate function on the field

try

IF(max([Distribution Upgrade])=1, num(Max(Date))-num(Today()) ) as [Days Left]

Re: Invalid Expression

effinty2112 — Sun, 03 Jul 2016 09:16:20 GMT

Hi Nir,

I would comment out that line and replace with the simple aggregation to get the Max(Date) per JobId. You could then create the field you want in the top part of a preceding load.

Table1:

Load

JobId,

IF([Distribution Upgrade]=1, num(MaxDate)-num(Today()) ) as [Days Left],

;

LOAD Distinct JobId,

num(Max(Date)) as MaxDate,

max([Distribution Upgrade]) as [Distribution Upgrade],

Resident Table

Group By JobId;

Good luck

Andrew

Re: Invalid Expression

Anonymous — Mon, 04 Jul 2016 05:24:42 GMT

Thank you everyone for helping, many thanks!

The problem was using an If statement on an attribute,

That was not part of the group by

the solution was breaking applying an aggregation function on it, see below:

Wrong:

Table1:

LOAD Distinct JobId,

IF([Distribution Upgrade]=1, num(Max(Date))-num(Today()) ) as [Days Left]

Resident Table

Group By JobId;

Correct:

Table1:

LOAD Distinct JobId,

IF(Max[Distribution Upgrade]=1, num(Max(Date))-num(Today()) ) as [Days Left]

Resident Table

Group By JobId;