topic Re: Loading accumulate totals in QlikView

Loading accumulate totals

Wed, 04 Jun 2014 11:07:25 GMT

Hi, I am trying to load a full accumulate in the data load script.

Example table:

Key, ItemPrice

1, 10

2, 20

3, 10

4, 100

5, 5

Desired output is:

Key, ItemPrice, CumTotal

1, 10, 10

2, 20, 30

3, 10, 40

4, 100, 140

5, 5, 145

How do I achieve this in the load script? I can't seem to get the peek and previous functions to work in my favour, always giving some weird results.

I also have a where clause in my dataset, I understand this may affect the functions used.

James.

Re: Loading accumulate totals

nagaiank — Wed, 04 Jun 2014 11:22:47 GMT

Try the following script, which works.

LOAD *,ItemPrice+If(IsNull(Peek(CumItemPrice)),0,Peek(CumItemPrice)) as CumItemPrice;
LOAD * Inline [
Key, ItemPrice
1, 10
2, 20
3, 10
4, 100
5, 5

];

Re: Loading accumulate totals

its_anandrjs — Wed, 04 Jun 2014 11:29:52 GMT

Then try with this

Raw:

LOAD * INLINE [

Key, ItemPrice

1, 10

2, 20

3, 10

4, 100

5, 5

];

Final:

LOAD

Key,

ItemPrice, if(RowNo() = 1,ItemPrice,ItemPrice + Peek(CumTotal)) as CumTotal

Resident Raw Order By Key;

DROP Table Raw;

Re: Loading accumulate totals

Wed, 04 Jun 2014 12:17:10 GMT

Final:

LOAD

Key,

ItemPrice, if(RowNo() = 1,ItemPrice,ItemPrice + Peek(AccItem)) as CumTotal

Resident Raw Order By Key;

DROP Table Raw;

In the above, why do you have Peek(AccItem).

AccItem isn't a field in this datamodel?

Re: Loading accumulate totals

its_anandrjs — Wed, 04 Jun 2014 12:20:28 GMT

Oh Sorry no it is CumTotal field check now i update

Final:

LOAD

Key,

ItemPrice, if(RowNo() = 1,ItemPrice,ItemPrice + Peek(CumTotal)) as CumTotal

Resident Raw Order By Key;

DROP Table Raw;

Re: Loading accumulate totals

jonathandienst — Wed, 04 Jun 2014 12:22:54 GMT

You can simplify this by dropping the preceding load and using Alt():

LOAD *,

ItemPrice+Alt(Peek(CumItemPrice),0) As CumItemPrice

Inline

[

Key, ItemPrice

1, 10

2, 20

3, 10

4, 100

5, 5

];

Peek() reads the value from the previous output row. Previous reads the value from the previous input row.

HTH

Jonathan

Re: Loading accumulate totals

Wed, 04 Jun 2014 13:53:21 GMT

This is correct and working as per my simplified example, can you explain why(how?) you're Peek()-ing a field which doens't exist yet? Surely you should Peek(ItemPrice)

It works but I have no idea why Hoping for elaboration so I can implement this into my bigger solution, but even if not many thanks for your time and help.

Re: Loading accumulate totals

Wed, 04 Jun 2014 14:11:57 GMT

My actual dataset is structured differently, how would this be different if I had

LOAD %Key,

dim_Something,

measure_Something,

measure_ItemPrice

FROM spreadsheet

WHERE testSomething>=1;

I have tried

LOAD %Key,

dim_Something,

measure_Something,

measure_ItemPrice,

measure_ItemPrice+Alt(Peek(CumItemPrice),0) As CumItemPrice

FROM spreadsheet

WHERE testSomething>=1;

but feel I'm really missing the point of what you're doing.