topic Re: sum with if not exist in QlikView

sum with if not exist

wunderch — Mon, 26 Jan 2026 18:19:17 GMT

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

Re: sum with if not exist

Qrishna — Thu, 27 Aug 2015 14:28:48 GMT

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

Re: sum with if not exist

wunderch — Thu, 27 Aug 2015 14:37:48 GMT

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')"?

Re: sum with if not exist

Qrishna — Thu, 27 Aug 2015 14:47:03 GMT

thousands like what?

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

Re: sum with if not exist

wunderch — Thu, 27 Aug 2015 14:56:20 GMT

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.

Re: sum with if not exist

Qrishna — Thu, 27 Aug 2015 14:59:17 GMT

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

WHERE NOT MATCH(Discountnummer,'4');

see attached. !

Re: sum with if not exist

Qrishna — Thu, 27 Aug 2015 15:01:23 GMT

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.

Re: sum with if not exist

wunderch — Thu, 27 Aug 2015 15:08:46 GMT

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

Re: sum with if not exist

Qrishna — Thu, 27 Aug 2015 15:15:09 GMT

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

Re: sum with if not exist

Peter_Cammaert — Thu, 27 Aug 2015 15:42:03 GMT

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

Re: sum with if not exist

settu_periasamy — Thu, 27 Aug 2015 17:02:24 GMT

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;

Re: sum with if not exist

wunderch — Fri, 28 Aug 2015 05:13:02 GMT

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

It works great.