Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks for your answer. That's what I looked for.
It works great.