topic Re: calculation with grouping in Load script in App Development

calculation with grouping in Load script

peisenrot — Thu, 09 Sep 2021 12:19:42 GMT

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

Re: calculation with grouping in Load script

sunny_talwar — Thu, 09 Sep 2021 12:46:58 GMT

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;

Re: calculation with grouping in Load script

peisenrot — Fri, 17 Sep 2021 08:35:30 GMT

thanks for your help. it is working now

Re: calculation with grouping in Load script

sunny_talwar — Thu, 23 Sep 2021 13:22:12 GMT

Awesome, glad it worked.