Error with IF statements

[Anonymous]

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!

[sunny_talwar]

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

[Anonymous]

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!

[sunny_talwar]

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?

[hic]

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

[Anonymous]

Ok..Let me try that

[Anonymous]

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!

[sunny_talwar]

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

[Anonymous]

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].

[maxgro]

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;