Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Kristle,
I'm not able to open your QlikView document.
Indeed, the document is corrupt. Please check it or upload again.
Peter
Cannot open this file
You can also post the part of your script that fails. That's more PE-friendly, as many community members are using a QV Personal Edition.
Peter
Hi,
The document couldn't be opened on my machine with the data,
If you are referring to the 'If' statement in the Mappings Tab, I would suggest you use a small set of data from "CustomerClassificationList.xls" in an inline table and trouble shoot your statement.
Do you use variables or fieldnames in these IF statements? Then make sure they exists at the moment of evaluation.
Are these IFs placed in a LOAD statement (should work with chart expression IFs as they are the same functions) or on the outer level of your script (doesn't work because those IFs are control statements with a different syntax).
Peter
you can attaching a image with the error
The file is corrupted
The problem is that you're using aggregation function max(ContractEndDate). I recommend to define this "max" as a variable before this load statement, and use the variable there.
Regards,
Michael