calculation with grouping in Load script

[peisenrot]

Hello,

I am creating a new field by caculation of soemthing with the formular 

LOAD
DocNumber,
if((Num(LastReviewed) > (num(today())-'730') and EffectiveDate = max([EffectiveDate])),'green',
if((Num(LastReviewed)>(num(today())-'1460') and EffectiveDate = max([EffectiveDate]))
and (Num(LastReviewed)<(num(today())-'730') and EffectiveDate = max([EffectiveDate])),'yellow',
if((Num((LastReviewed))<(num(today())-'1460') and EffectiveDate = max([EffectiveDate])),'red')))
as PriorityAnalyse

resident EOS_Documents
group by DocNumber ;

I have for each DocNumber one or more EffectiveDate and will only consider for the formular if the EffectiveDate is the biggest.

But system is not allowing this and say it is invalide expression. Where is something one.

thanks for help

[sunny_talwar · Accepted solution]

Try this instead

Left Join (EOS_Documents)
LOAD DocNumber,
     Max(EffectiveDate) as MaxEffectiveDate
Resident EOS_Documents
Group By DocNumber;

New_EOS_Documents:
LOAD *
if(LastReviewed > Today()-730 and EffectiveDate = MaxEffectiveDate, 'green',
if((LastReviewed > Today() - 1460 or LastReviewed < Today() - 730) and EffectiveDate = MaxEffectiveDate, 'yellow',
if(LastReviewed < Today() - 1460 and EffectiveDate = MaxEffectiveDate, 'red')))
as PriorityAnalyse
Resident EOS_Documents;

DROP Table EOS_Documents;

[peisenrot]

thanks for your help. it is working now

[sunny_talwar]

Awesome, glad it worked.