Announcements
cancel
Showing results for
Did you mean:
Contributor III

## calculation with grouping in Load script

Hello,

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

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

1 Solution

Accepted Solutions
MVP

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

New_EOS_Documents:
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;``````
3 Replies
MVP

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

New_EOS_Documents:
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;``````
Contributor III
Author

thanks for your help. it is working now

MVP