IF statement conflict/error in script

[#1]

Hello;

Please find hereby a file containing a script with two IF statements.

When loading the script the system returns the error:  Invalid Expression.

That is where I need your help to understand what is going wrong.

The same statements in a straight chart work like a charm.

Many thanks in advance.

Kristel

Script:

Maintenance_contracts:

LOAD

     %Equipment_EQUNR,

     [Contract Nr] as Contract,   

     [Contract Line] as [Contract Item],

     [Contract Nr] &'-'&[Contract Line]&'-'&%Equipment_EQUNR&'-'&ContractStartDate&'-'&ContractEndDate as [Contract num & item & eqp & dates],

     ContractStartDate as [Contract Start Date],

     ContractEndDate as [Contract End Date],

     If(((Date(MonthStart([ContractStartDate]),'MMM YYYY')-(max(ContractEndDate)))<90) and ((Date(MonthStart([ContractStartDate]),'MMM YYYY')-(max(ContractEndDate)))>0),1,0) as InRange,

     if((MonthStart(ContractStartDate)=(monthstart(Date(MonthStart([ContractStartDate]),'MMM YYYY')))),1,0) as Renewed,

     ContractStartDate-ContractEndDate as [Dif cont start & end],

     Year([ContractStartDate]) as [Contract Start Year],

     Month([ContractStartDate]) as [Contract Start Month],

     Date(MonthStart([ContractStartDate]),'MMM YYYY') as MonthYear,

     [Contract Type] as Contract_type_Test, //ZWAR-WV   

     %SerialLink_ContractItemKey as %Contract_notif_key,    

     [Contract Mat] as [Contract Material],

  if(SubStringCount([Contract Mat], 'EXT') > 0, 'Extended Warranty cont.',

  ApplyMap('Contract_type_map', [Contract Type])) as [Contract Type],

     [Sales Org._VKORG] as [Contract Sales Org] 

//     Item_POSNV,

//     Description_KTEXT,

//     Item_VPOSN,

//     %SerialLink_ObjDataKey,

//     %SerialLink_ContractItemKey,

Working in chart:

=If((($(vMonthYear)-(max([Contract End DateWV])))<90) and (($(vMonthYear)-(max([Contract End DateWV])))>0),1,0)

=if((MonthStart([Contract Start DateWV])=(monthstart($(vMonthYear)))),1,0)

variable vMonthYear = MonthYear

[Peter_Cammaert · Accepted solution]

Indeed. In your chart, the max() function aggregates from a field that contains a range of values.

In a LOAD statement, there is only one value in the current record and aggregation functions cannot be used without a GROUP BY clause.

Put the ContractEndDate in a variable, like Michael suggests. Or if you have multiple ContractEndDates, create a mapping table with for each contract a single maximum ContractEndDate. Example code to create this table and that should come before you use applymap():

MapMaxContractDates:

MAPPING LOAD [Contract Nr], max(ContractEndDate)

RESIDENT / FROM datasource

GROUP BY [Contract Nr];

Code that replaces the max() function in your LOAD statement:

   ... - applymap('MapMaxContractDates', [Contract Nr]) ...

Good luck,

Peter