topic AW:Re: AW:Re: Aggregation over more then 1 field with script in QlikView

Aggregation over more then 1 field with script

Tue, 21 Dec 2010 18:05:45 GMT

Hallo QV Community,

I can not find out how to aggregate over more the one field with a script. In the application there ist the rangesum() function, which ist very helpful, but do not show the result I whish. I have build a simple sample, which shows the table to begin: In the period (Periode) 0 has every article (Artikel) a begining inventory value (bestand1).

Now I want to generate a table with following result:

For each period and article the values should be aggregated: (article a1 for example:)

150 [period 0]+ (-35)[period1] = 115 + (-1)[period3] = 114 + (-9)[period6] =103

I think that must be very simple, but can not find out how...

Can someone help me?

Aggregation over more then 1 field with script

johnw — Wed, 22 Dec 2010 01:59:22 GMT

I believe this:

LOAD
Periode
,Artikel
,rangesum(bestand1,if(Artikel=previous(Artikel),previous(bestand))) as bestand
FROM wherever
ORDER BY Artikel, Periode
;

AW:Re: Aggregation over more then 1 field with script

Wed, 22 Dec 2010 11:55:54 GMT

Thanks for your idea, but it does not generate the result i need. The first step ist to sort the table for 'Artikel' and 'Period' like your suggestion:

But then I think, that we need a variable which saves the calculated value. Then using this variable in a do while function approximately:


let v_bestand = '0';
load Artikel, Period,
do while { Artikel=previous(Artikel)
 ($(v_bestand)=$(v_bestand)+bestand1) as bestand2
 }
 else
 {
 $(v_bestand) = 0
 bestand1 as bestand2
 }

I don't know ho to write this in correct qlikview syntax. (PS: How can I remove the space in new lines?)

AW:Re: Aggregation over more then 1 field with script

johnw — Wed, 22 Dec 2010 17:43:50 GMT

Sorry. One of the previous() needed to be peek(). That fixed, it produces the table you requested, other than 6 a1 which you miscalculated (114 - 9 = 105, not 103). You don't need a variable, you don't need to save the calculated value (it's already saved in the previous row of your output table), and you don't need a do while loop. All you need to do is reference the previous value in the OUTPUT table, which is what peek() does, while previous() references the previous value in the INPUT table. I appear to have confused these in my previous post.

See attached.

As for removing the spaces in the new lines, either cut and past into notepad and then into your post, or go into settings -> user preferences -> editor, and remove the checkmark next to "copy as RTF". Any editing in the post itself will I think reinsert blank lines. There's probably a way around that somewhere in the preferences for the site.

AW:Re: AW:Re: Aggregation over more then 1 field with script

Wed, 22 Dec 2010 18:23:24 GMT

Thank you very much ! 🙂

As I tested the rangesum() in script editor with peek() like your solution, I thought, that I have to take a existing field (red marked):

,rangesum(bestand1,if(Artikel=previous(Artikel),peek(bestand1))) as bestand2

The second issue is, that the name of the calculated field also has to be named like the green marked "variable". Otherwise the function rangesum() do not work...

,rangesum(bestand1,if(Artikel=previous(Artikel),peek(xyz))) as xyz

Where could I get this information? I looked up in the help file and the manual, but this stood nowhere 😞

AW:Re: AW:Re: Aggregation over more then 1 field with script

johnw — Wed, 22 Dec 2010 18:33:15 GMT


TimurVogel wrote:Where could I get this information?

I'm not sure there's anything that will tell you which field names and functions to use to produce your desired result. It's just a matter of experience with QlikView, I think. The forums are usually a good resource when you're stuck, or even if you figure out a way to do it, but it seems inelegant to you. Sometimes you just need to do something inelegant, but sometimes there's a more elegant way.