Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]
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.
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]
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
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;