Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF statement conflict/error in script

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,

Error.jpg

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

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

10 Replies
Luis_Cortizo
Former Employee
Former Employee

Kristle,

   I'm not able to open your QlikView document.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Indeed, the document is corrupt. Please check it or upload again.

Peter

manideep78
Partner - Specialist
Partner - Specialist

Cannot open this file

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

srchilukoori
Specialist
Specialist

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

you can attaching a image with the error

MK_QSL
MVP
MVP

The file is corrupted

Anonymous
Not applicable
Author

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