# QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

New Contributor III

## sum with if not exist

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

1 Solution

Accepted Solutions

## Re: sum with if not exist

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

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

11 Replies
Valued Contributor III

## Re: sum with if not exist

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

New Contributor III

## Re: sum with if not exist

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

Valued Contributor III

## Re: sum with if not exist

thousands like what?

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

New Contributor III

## Re: sum with if not exist

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.

Valued Contributor III

## Re: sum with if not exist

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

WHERE NOT MATCH(Discountnummer,'4');

see attached. !

Valued Contributor III

## Re: sum with if not exist

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.

New Contributor III

## Re: sum with if not exist

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:

Order,

Discountnumber

group by Order where Discountnumber<>4

??

Valued Contributor III

## Re: sum with if not exist

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

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

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