Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: IF statement conflict/error in script

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

10 Replies
Employee
Employee

Re: IF statement conflict/error in script

Kristle,

   I'm not able to open your QlikView document.

Re: IF statement conflict/error in script

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

Peter

manideep78
Valued Contributor

Re: IF statement conflict/error in script

Cannot open this file

Re: IF statement conflict/error in script

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
Valued Contributor

Re: IF statement conflict/error in script

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.

Re: IF statement conflict/error in script

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

dagomezl
Contributor III

Re: IF statement conflict/error in script

you can attaching a image with the error

Re: IF statement conflict/error in script

The file is corrupted

mov
Esteemed Contributor III

Re: IF statement conflict/error in script

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

Community Browser