Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Hi community!

I need a kind of a stack in qlikview,...

I have a table with amount + date, which is chronologically.


I want a new field, where all items  within the 200 bucks limit(chronologically) have an discount of %10, otherwise 1% discount)

This doesn't work: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

in Qlikview I can not use sum() within an if statement

1 Solution

Accepted Solutions
jykang0638
New Contributor III

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Hi, try the below.

LOAD Product, Amount, Price, Date
, NumSum(Amount, Peek('Sum_Amount')) as Sum_Amount
, If(NumSum(Amount, Peek('Sum_Amount')) < 200, Price * 0.9, Price * 0.99) as New_Price
Resident Products
Order By Date asc;

It's just example code, so adjust to your env.
I hope it will help you.

19 Replies
jvitantonio
Valued Contributor III

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Do you want this in the  script? This is, add a new fild on your table with the new price?

If(amount<200, amount*0.1, amount*0.01) as NewPrice

Not applicable

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

If this is in the script you can only do sums if it has a group by clause.

If it isn't in the script you might be looking for aggr,

sum(aggr(if(sum(amount)<200,sum(amount/10),sum(amount/100)),item))

Not applicable

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

No, it's hard to explain

The first products ( Date - chronologically )  which are in summary under 200 should get an discount of 10%, the rest should get only 1%.
Yes it's in the script

Hopefully you unterstood me
Thanks and regards

Not applicable

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Do you have a group by clause? You will need to do a resident load Group by section to be able to use sum in the script.

I do not really understand the

The first products ( Date - chronologically )

part. Is it only a certian amount of products that you want this applied to, or all products where the total sum is under 200?

Not applicable

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

ok for example:

Limit: 200

Table:  Amount    -  Date

product1: 50   -  1.2.2012

product2: 60   -  3.2.2012

product3: 80   -  4.2.2012

product4: 40   -  5.2.2012

product5: 10   -  6.2.2012
====================

               240

so products 1,2,3 should get 10% discount ( first products wich are under the limit = 200 )

product 4 +5 should  get 1% discount.


How do I realize it?


This way is much better for me:

The summary of the value which is under 200 should get 10%  and the rest value (40 ) should get 1%

So  the first product ( 1,2,3,  1/4 of 4 ) should get 10%

the rest ( 3/4 of product 4, product 5 ) should get 1%.

That means:  total value of 200 gets 10%

                      the rest value ( 40) gets 1%

I know that I have to use group by,  but if i combine Sum()  and an if statement  it gives me an error...

Not applicable

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

I am not sure if this is possible in the script, if it is then it is beyond my skill level.

Not applicable

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

yes that's right, normaly I would need somethink like a stack and put one by one out, to fill the limit of 200,  but in qlikview it's totally difficult to realize that.

Not applicable

Re: sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Do you have a set number of products you are working with, and will it always be product 1 on day 1 product 2 day 2 ect.?

sebastiandperei
Valued Contributor

sum(if(sum(amount)<200,amount/10,amount/100) as newPrice

Hi, try:

Products:

Load

     Product,

     Amount,

     Price,

     Date,

     RowNo() as Ord

Resident ProductsTemp

Order By Date Asc;

Let Acc = 0;

For A = 1 to NoOfRows('Products')

     Let PrAmount = Peek('Amount',$(A),'Products');

     Let Acc = $(Acc) + $(PrAmount);

     If $(Acc)>200 then

          Let OrdLim = Peek('Ord',$(A),'Products');

          Exit For;

     End If;

Next;

Left Join (Products)

Load

     *,

     If (Ord>=$(OrdLim), 0.09, 0.9)*Price as NewPrice;

Resident Products;

Please, correct sintaxys if im wrong, because im writing directly here.

Tell me if it works.

Community Browser