topic IF statement conflict/error in script in QlikView

IF statement conflict/error in script

Mon, 03 Mar 2014 15:30:23 GMT

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

Re: IF statement conflict/error in script

Luis_Cortizo — Mon, 03 Mar 2014 15:33:53 GMT

Kristle,

I'm not able to open your QlikView document.

Re: IF statement conflict/error in script

Peter_Cammaert — Mon, 03 Mar 2014 15:40:07 GMT

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

Peter

Re: IF statement conflict/error in script

manideep78 — Mon, 03 Mar 2014 15:40:24 GMT

Cannot open this file

Re: IF statement conflict/error in script

Peter_Cammaert — Mon, 03 Mar 2014 15:41:26 GMT

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

Re: IF statement conflict/error in script

srchilukoori — Mon, 03 Mar 2014 15:42:13 GMT

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

Peter_Cammaert — Mon, 03 Mar 2014 15:46:19 GMT

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

Re: IF statement conflict/error in script

Anonymous — Mon, 03 Mar 2014 15:51:25 GMT

you can attaching a image with the error

Re: IF statement conflict/error in script

MK_QSL — Mon, 03 Mar 2014 17:15:52 GMT

The file is corrupted

Re: IF statement conflict/error in script

Anonymous — Mon, 03 Mar 2014 17:27:04 GMT

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

Re: IF statement conflict/error in script

Peter_Cammaert — Mon, 03 Mar 2014 17:40:11 GMT

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