Invalid Expression

[Anonymous]

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

[vinieme12]

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.

[lironbaram · Accepted solution]

hi

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]

[effinty2112]

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

[Anonymous]

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;