Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

19 Replies
jykang0638
Partner - Contributor III
Partner - Contributor III

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.

sebastiandperei
Specialist
Specialist

jykang, sorry, but the field Sum_Amount is created at the end of the sentence. You can`t reference it inside the sentence.

johnw
Champion III
Champion III

sebastiandpereira wrote:

jykang, sorry, but the field Sum_Amount is created at the end of the sentence. You can`t reference it inside the sentence.

Actually, yes you can.

Jykang's basic solution is sound, is how I independently thought to solve it when I saw the question, and it works with some minor fixes to the actual script.  The reason it can work is that the peek() function is looking at the record you created before the current record, which does have a Sum_Amount established, so we're still following the basic principle of "you must establish a field before you can reference it", no matter how funny the script might look.  It makes me sad how often I see people reject a correct solution, without trying it, because of something they think they know about how QlikView works.  OK, yes, 90% of the time, you'll probably be right.  But the other 10% of the time, you'll learn something new.  It's that 10% of the time that's so valuable.

Anyway, the script needs a little modification to do the partial discounting that was mentioned, and that'll be easer if we use a preceding load.  This seems to work:

LOAD *
,if(Cumulative<=200,Amount*.9
,if(peek('Cumulative')>=200,Amount*.99
,(200-peek('Cumulative'))*.9+(Cumulative-200)*.99)) as "Discounted Amount"
;
LOAD *
,rangesum(Amount,peek('Cumulative')) as Cumulative
INLINE [
Product, 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
];

See attached.

All that said, I don't think QlikView should be doing any accounting for you, and I consider calculating discounts to be accounting.  That's what an accounting system is for.  QlikView is just a reporting system.

(Edit: Added single quotes in the peek() function above.  It's supposed to be a text literal.  QlikView interprets it correctly without the single quotes, but I think a good habit is to ALWAYS use single quotes for text literals.)

Not applicable
Author

thanks guys! I'll try it on monday,  sounds good

jykang0638
Partner - Contributor III
Partner - Contributor III

Hi, everyone,

I think John's solution is more sophisticated than mine.
And I am very impressed with his talk: It's that 10% of the time that's so valuable.
Thanks John!

sebastiandperei
Specialist
Specialist

Jykang, excuse me, i didn't want to be offensive (sorry for my english). Please, sorry.

John, you are right, I didn't test the Jykang comment. But, why you don't just comment your solution? I know you are the best in qlikview who really know how the qlikview works, and I prefer you to explain the changes in Jykang solution (I am really interested in this script, is easier than my solution with For), than read about how the people must write in forums.

And, excuse me for make you sad... It was only a comment... Make sad because of hunger in the world!!

johnw
Champion III
Champion III

sebastiandpereira wrote:

...I prefer you to explain the changes in Jykang solution (I am really interested in this script, is easier than my solution with For)...

You mean explain how it works, and the minor differences between my and jykang's solution?

The script processes records in order, so we start at the first record.  We do a peek(Cumulative) even though we've never established a value for that field.  One thing that jykang got right and I got wrong is that I don't have single quotes - the parameter here is a text literal, not a field.  I try to make a habit of always putting text literals in single quotes, even where QlikView allows me to leave the quotes off.  So whoops.  Anyway, instead of an error, peek('Cumulative') returns null when we're on the first row.  And unlike previous(), peek() refers to your output table instead of the input table, and can thus refer to fields you are creating as part of the output table.  The null doesn't appear to be documented, and the "output table" part appears to be incorrectly documented, but it's this behavior that makes peek() so valuable in many cases, such as this one. 

Mind you, you're probably taking a risk using peek() this way, because some day some QlikTech employee may look at where the documentation says "in the input table" (emphasis added), compare it to the actual behavior, and then "correct" the actual behavior to match the documentation.  But I'm hoping QlikTech is smarter than that, and simply fixes the documentation, since the current behavior is why peek() is such a great function.

I used rangesum() instead of numsum() because, according to the help text, "The numsum function is now obsolete and is superseded by the rangesum function."  I used rangesum() intead of a simple + sign because we know that on the first record, we want to take only the Amount value, and ignore the Cumulative value, which is null.  Adding to a null always creates another null.  But rangesum treats null as 0, so it does what we need.  We could have done something like Amount + if(recno()>1,peek('Cumulative'),0), but that's more complicated.  That's the basic idea of what we're doing, though, and might arguably be easier for someone to maintain because it's more clear what's happening.

Jykang also used a field Price, which might well be correct in the real data, but wasn't in the sample data.  To keep it simple, I used Amount everywhere.

I didn't include an order by.  We'll probably need one in the real data, but I don't know what order we want.  Perhaps it's by date or more likely by a timestamp.  Perhaps it's something else.  Since I didn't know, I left it off.  Perhaps I should have included it like jykang did to indicate that one would likely be required.

The other difference is to handle the discounted amount only applying to the first 200, even if that meant it applied to part of record and not the whole thing.  I suspect there's a cleaner way, but I checked for that case explicitly, and then split the amount into what was before and after 200 so that I could apply the correct discount to each part.

sebastiandperei
Specialist
Specialist

Thanks for the patience John...

Sorry if I'm doing a stupid question, but i can't understand why you use two sentences:

Load.....;

Load...

Include....;

, Jykang used only one sentence. What it means? You are Loading from nothing? What QV does between both tables? Join ? Concat?

I'm sure that it was a fool question, sorry.

In second place, are you talking about the use of "peek" within the "For"? I have used like that a lot of times... I have understand that this sintaxis were correct, because help says that. What another suggestions do you have about my answer? I would like to know other opinions, because i use loops a lot of times...

Thanks again

johnw
Champion III
Champion III

The source for the first load is the second load.  The source for the second load is the inline table.  The first load is called a "preceding load".  You can stack as many of these as you want, and it's a common way of getting around the restriction that a field must be created before you can refer to it.  So you create the field in one load, and you refer to it in the preceding load.  In my experience, this is significantly faster than trying to left join the same information after the table has already been built.  Preceding loads don't seem to be well documented, but I assume they're used in examples in the reference manual, and they're certainly used in examples on the forum.  I've also noticed the restriction that combining a preceding load with a crosstable load just doesn't work, so we can't say that a preceding load universally loads from the output of the load below it, even if that's the basic behavior.  It's possible that's just a bug in the product, though.

In my case, I used a preceding load to refer to field "Cumulative" in the preceding load.  I could have done it all in one load by repeating the rangesum(Amount,peek('Cumulative')) part of the expression a couple times, but I prefer to not write the same code in multiple places where I have a reasonable alternative.  You can see how jykang was forced into repeating his NumSum(Amount,Peek('Sum_Amount')) since he only used a single load statement.  It's not really a big deal either way for an expression this simple, particularly since you can visually stack one on top of the other to make it very clear that if one changes, so should the other.  I do it both ways, whichever seems cleaner to me for the specific case.

Any reference I made to peek() was in reference to my or jykang's code.  I was not referring to your code.

Looking at your code, your idea certainly seems sound, though I think I see a couple simple bugs.  Testing it out now.  Yeah, your code works fine with a few trivial modifications.  When using peek(), records are numbered starting with 0, so you need to address that in some way, such as by looping from 0 to the number of rows - 1.  You need 0.99 instead of 0.09.  And you should drop ProductsTemp at some point.  And of course it doesn't handle that borderline case of splitting the discount across the 40 amount, but you probably weren't trying to do so.  As far as suggestions, I'd just suggest that you don't need a loop in this case, that there is a simpler solution.

I almost never use loops, or at least not explicit loops like that.  I do use a few while loops, but I feel like those fit more cleanly into the load structure.  I'm not saying that there's anything wrong with explicit loops when they're called for, just that there may often be a simpler alternative. 

You might also try some performance testing if you find what seems to be an alternative to one of your loops.  I can't remember performance testing it myself, but my guess is that the explicit loop is slower than some alternatives.  On the other hand, I think I recall making the same prediction about while loops, and performance testing showing that I was wrong, that they are extremely fast.  I like to think that I'm right more often than wrong with my performance predictions, but I'm wrong very, very often.  Testing is always called for when performance is critical.

One reason I don't typically like loops is that for large amounts of data, they can overwhelm the logfile.  In this case, though, we're not going to have to loop through many records before exiting the loop, and seeing the loop details in the log may actually be quite useful for debugging.  So in this specific case, I'd consider that an advantage of your approach.

sebastiandperei
Specialist
Specialist

John, thanks you very much for the explaining.

I found very useful the Preceding Load !, i gues that it would help me in some cases.

About Loops, I have used it when I didn't found one solution...

For example. One customer had needed to clasify a group of customers in 4 categories (A, B, C and D), in accordance of their sales in last 28 days (A most sales, D less sales). They doesn't had an algoritm, becaouse of they have used her common sense. Later than a time of analysis, my solution was:

- The limit between B and C is the Median (not Average, because in some cases, the most sellers were the 70% of toe total sales). So, since the Median Value, the limit could be higher or lower, searching a heavy slope

- Limit between B and A is calculated with the heavier slope between 1rst place and the B-C limit.

- Limit between D and C is calculated with the heavier slope between last place and the B-C limit.

- Every slope is weighted with two variables (up and down) that depends on the difference with last or next, and their sales. This is to make a stable measurement.

- I did that with 3 loops (with IF, Load, Peek's, etc...). Each one sets the limits.

- Then, one load get the group to each customer.

Do you have some similar case? Now, for the same people, i'm doing a Stock Redistribution App, who manages the stock for their subsidiaries... there are a lot of Bussiness rules!!! Is a kind of AI, who will decide the best strategy between most cases...

So, thanks John!!!   And I repeat, i don't wonted to make you sad... sorry for this.

Sebastián.