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

[Qrishna]

sEE ATTACHED AND LET  ME KNOW IF YOU ARE EXPECTING THE SAME OUTPUT.

[wunderch]

Yes in the table it is the right output, but the excel is only a an example for my problem.

In real i have thousand of orders and i cant select them with an match.

Is there a chance to get the same result without "MATCH(Order,'123')"?

[Qrishna]

thousands like what?

there should be some way to recognize right?what is that.

[wunderch]

sorry. thousand of orders, some have discountnumber 4 and some not. And i need only the orders without discountnumber 4.

So i think i have to select them in the expression.

[Qrishna]

if you want all the orders with no discountnumber 4 then you can just use

WHERE NOT MATCH(Discountnummer,'4');

see attached. !

[Qrishna]

And on suggestion:

If data transformation is possible at script level, then do it at the script level itself.

on the front end it consumes relatively a lot of effort for qlikview to perform tranformations.

[wunderch]

Thanks for your example, but i only want to get the order "123" because this order have no orderpos with discountnumber 4.

Is there a chance to get this with "group by"

Something like this:

Load Distinct

Order,

Discountnumber

group by Order where Discountnumber<>4

??

[Qrishna]

if you want order =123 and discountnumber<>4, you have to put those filters in the script as shown in the 1st qvw.

if you have thousands of orders and dont know wht orders to be filter, try to figure out the way which gives the the orders to be filtered.

then we can look for some solution.

Thanks

[Peter_Cammaert · Accepted solution]

In a chart with just Order as dimension, add this expression:

=aggr(if (WildMatch(Concat(Discountnummer, ';'), '*4*') = 0, sum(Amount)), Order)

Some adjustments may be required if you add dimensions.: