Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

wunderch
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)

Some adjustments may be required if you add dimensions.:

11 Replies
krishna_2644
Valued Contributor III

Re: sum with if not exist

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

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

krishna_2644
Valued Contributor III

Re: sum with if not exist

thousands like what?

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

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

krishna_2644
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. !

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

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

Load Distinct

Order,

Discountnumber

group by Order where Discountnumber<>4

??

krishna_2644
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)

Some adjustments may be required if you add dimensions.:

Community Browser