topic Re: Error with IF statements in QlikView

Error with IF statements

Anonymous — Wed, 25 Mar 2015 18:23:59 GMT

Can someone please tell me what's wrong with this piece of code

If([Item_POSNR]='CC0000',If([Package Type]='Charter',100,If([Package Type]='Scheduled',1,0)), // ERROR
If([Item_POSNR]='TI0000',100, // ERROR
Sum(If(Match([VBAP Material Group_MATKL],'01','60'),100,0)+ // ERROR
If(Match([VBAP Material Group_MATKL],'02','03','04','62','70'),1,0)))) as TypeSum // ERROR

Thanks!

Re: Error with IF statements

sunny_talwar — Wed, 25 Mar 2015 18:30:18 GMT

Are you using some kind of grouping in your script for the table in which you use this if statement? You are using Aggregation here (Sum) and you need to have a group by statement after From/Resident

HTH

Best,

Sunny

Re: Error with IF statements

Anonymous — Wed, 25 Mar 2015 18:36:47 GMT

Here is the complete code with group by statement after Resident

[Booking Type]:
Load
[Sales Document],
Sum(If([VBAP Material Group_MATKL]='10' and [VBAP Higher-level item_UEPOS]>'000000',[VBAP Order Quantity_KWMENG],0)) as [Room Nts],

If([Item_POSNR]='CC0000',If([Package Type]='Charter',100,If([Package Type]='Scheduled',1,0)), // ERROR
If([Item_POSNR]='TI0000',100, // ERROR
Sum(If(Match([VBAP Material Group_MATKL],'01','60'),100,0)+ // ERROR
If(Match([VBAP Material Group_MATKL],'02','03','04','62','70'),1,0)))) as TypeSum // ERROR

Resident [Booking Item]
Group by [Sales Document];

Can you please tell me what I am doing wrong here..

Thanks!

Re: Error with IF statements

sunny_talwar — Wed, 25 Mar 2015 18:39:44 GMT

This somehow doesn't seem right to me:

Sum(If(Match([VBAP Material Group_MATKL],'01','60'),100,0)+ // ERROR
If(Match([VBAP Material Group_MATKL],'02','03','04','62','70'),1,0))))

Why do you need this sum here? there is not field you are summing here... are you?

Re: Error with IF statements

hic — Wed, 25 Mar 2015 18:52:17 GMT

You use "Group By [Sales Document]". But in your Group By, you need to include all fields that aren't inside an aggregation function. I.e. also [Item_POSNR] and [Package Type].

HIC

Re: Error with IF statements

Anonymous — Wed, 25 Mar 2015 18:56:50 GMT

Ok..Let me try that

Re: Error with IF statements

Anonymous — Wed, 25 Mar 2015 19:06:02 GMT

Dependent upon the Material Group, I add either 100 or 0 + 1 or 0 as TypeSum. Here’s what happens right after this load (see below all in light gray)…

[Booking Type]:

Load

[Sales Document],

Sum(If([VBAP Material Group_MATKL]='10' and [VBAP Higher-level item_UEPOS]>'000000',[VBAP Order Quantity_KWMENG],0)) as [Room Nts],

If([Item_POSNR]='CC0000',If([Package Type]='Charter',100,If([Package Type]='Scheduled',1,0)), // ERROR

If([Item_POSNR]='TI0000',100, // ERROR

Sum(If(Match([VBAP Material Group_MATKL],'01','60'),100,0)+ // ERROR

If(Match([VBAP Material Group_MATKL],'02','03','04','62','70'),1,0)))) as TypeSum // ERROR

Resident [Booking Item]

Group by [Sales Document];

Left join ([Booking Header])

Load

[Sales Document],

[Room Nts],

If(TypeSum>=100,'Charter',If(TypeSum>=1,'Scheduled','Land Only')) as BkgType

Resident [Booking Type];

Drop Table [Booking Type];

Thanks!

Re: Error with IF statements

sunny_talwar — Wed, 25 Mar 2015 19:09:51 GMT

You are adding two static values here, you don't need sum by putting the plus sign you are essentially adding two static values. I think your expression might work if you try to remove the Sum from the equation.

Best,

Sunny

Re: Error with IF statements

Anonymous — Wed, 25 Mar 2015 19:10:21 GMT

This is a copy of the original script…

[Booking Type]:
Load
[Sales Document],
Sum(If([VBAP Material Group_MATKL] = '10' and [VBAP Higher-level item_UEPOS] > '000000', 1, 0))
as [Room Nts],
Sum(If(match([VBAP Material Group_MATKL], '01', '60'), 100, 0)
+ If(match([VBAP Material Group_MATKL], '02', '03', '04', '62', '70'), 1, 0)) as TypeSum
Resident [Booking Item]
GROUP BY [Sales Document];

Left join ([Booking Header])
Load
[Sales Document],
[Room Nts],
If( TypeSum >= 100, 'Charter',
If(TypeSum >= 1, 'Scheduled', 'Land Only') ) as BkgType
Resident [Booking Type];

DROP Table [Booking Type];

…notice the GROUP BY for [Booking Type]: Only [Sales Document].

Re: Error with IF statements

maxgro — Wed, 25 Mar 2015 19:31:40 GMT

you still have a group by with fields without aggregation

you should have

load

a, b, c,

sum(x),

count(y)

from ....

group by a,b,c;

this doesn't work

load

a, b, c,

if(Item_POSNR=1,sum(1),sum(0)), // error

sum(x),

count(y)

from ....

group by a,b,c;

Re: Error with IF statements

anbu1984 — Thu, 26 Mar 2015 05:39:40 GMT

This script looks correct to me. Do you still have issues with the script?

[Booking Item]:

LOAD * Inline [

Sales Document,VBAP Material Group_MATKL,VBAP Higher-level item_UEPOS

1,2,3 ];

[Booking Type]:

Load

[Sales Document],

Sum(If([VBAP Material Group_MATKL] = '10' and [VBAP Higher-level item_UEPOS] > '000000', 1, 0))

as [Room Nts],

Sum(If(match([VBAP Material Group_MATKL], '01', '60'), 100, 0)

+ If(match([VBAP Material Group_MATKL], '02', '03', '04', '62', '70'), 1, 0)) as TypeSum

Resident [Booking Item]

GROUP BY [Sales Document];

Re: Error with IF statements

Anonymous — Thu, 26 Mar 2015 06:13:47 GMT

[Booking Type]:
Load
[Sales Document],
If([VBAP Material Group_MATKL] = '10' and [VBAP Higher-level item_UEPOS] > '000000', 1, 0)as [Room Nts],// there was no field on which you were summing so no sum aggregation required
If(match([VBAP Material Group_MATKL], '01', '60'), 100, 0)
+ If(match([VBAP Material Group_MATKL], '02', '03', '04', '62', '70'), 1, 0) as TypeSum

// there was no field on which you were summing so no sum aggregation required
Resident [Booking Item];
// you dont need Group by as though you say typesum but you are not adding fields you are adding static values which is not aggregation.

Left join ([Booking Header])
Load
[Sales Document],
[Room Nts],
If( TypeSum >= 100, 'Charter',
If(TypeSum >= 1, 'Scheduled', 'Land Only') ) as BkgType
Resident [Booking Type];

DROP Table [Booking Type];