Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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!
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?
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
Ok..Let me try that
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!
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
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].
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;