sum with if not exist

[wunderch]

Hi,

I have the following table: (Excel: TESTCW_NL)

Now I want to get only the order "123", because in this order is no Discountnumber 4.

So for example, the formula have to look something like this

sum(if( aggr(Discountnumer, Order)<>4, Amount)

Can anyone help me?

Regards

Christian

[settu_periasamy]

Hi,

Check this script

A:
Load Distinct *,If(Discountnummer=4,1) as Flag;
LOAD * INLINE [
    Order, OrderPos., Amount, Discountnummer, Discount
    123, 1, 100, 3, 40
    123, 1, 100, 5, 50
    456, 1, 80, 4, 20
    456, 2, 50, 3, 10
    789, 1, 200, 3, 20
    789, 1, 200, 4, 10
    789, 1, 200, 5, 30
];

B:
Mapping Load Distinct Order,Flag  
  Resident A where not isnull(Flag);

NoConcatenate

C:
LOAD  Order, 
   OrderPos., 
   Amount, 
   Discountnummer, 
   Discount,
   ApplyMap('B',Order,Null()) as Flag 
  Resident A;
DROP Table A;

NoConcatenate
Final:
LOAD Order, 
  OrderPos., 
  Amount, 
  Discountnummer, 
  Discount
  Resident C Where IsNull(Flag);


DROP Table C;

[wunderch]

Thanks for your answer. That's what I looked for.

It works great.