topic Acculumation in Load Script in QlikView

Acculumation in Load Script

Thu, 17 Sep 2009 05:42:08 GMT

I am looking to accumulate a value during my Load Script

HERE IS MY SCRIPT
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Transactions:
LOAD * INLINE [
Flight, DaystoDeparture, DepDate, Sold
SK100, 30,09/18,6
SK100, 30,09/18,1
SK100, 20, 09/18,1
SK100, 10, 09/18,4
SK100, 5, 09/18,4
SK100, 1, 09/18, 5
DL100, 30, 09/18,4
DL100, 20, 09/18,2
DL100, 10, 09/18,8
DL100, 5, 09/18,8
DL100, 1, 09/18,10
];

Curve:
LOAD
Flight, DaystoDeparture, DepDate,
Sum(Sold) as Total_Booking,
numsum( Sold, peek( 'Bsum' ) ) as Bsum
RESIDENT Transactions
GROUP by Flight, DepDate, DaystoDeparture
ORDER by Flight, DepDate desc, DaystoDeparture desc

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

In the QV Help there is the function numsum with the example:

Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;

creates an accumulation of B in Bsum.

This is what I am looking to do. It looks like I can use the 'Bsum' field in an expression even though it is created in the load statement, and not from the underlying data source.

Any help apprecaited.

Acculumation in Load Script

Oleg_Troyansky — Thu, 17 Sep 2009 07:29:36 GMT

Function numsum has been replaced with the function RangeSum(). The rest of your assumptions are correct - you can accumulate values this way. You might want to order your data in a certain way, and if you have any other key fields (Item, or Customer, etc...) - you might need to check if the previous(key) equals to the current value of the Key.

Cheers!

Acculumation in Load Script

Fri, 18 Sep 2009 01:32:06 GMT

HI Oleg,
Thanks for helping out.

I changed it to the following and it Loads without error, but my results are not what i expected.

Curve:
LOAD
Flight, DaystoDeparture, DepDate,
Sum(Sold) as Total_Booking,
sum(Previous(Sold)) as Prev_Sold,
only(rangesum(Sold, Peek('Total_Curve'))) as Total_Curve
// numsum( Sold, peek( 'Bsum' ) ) as Bsum
RESIDENT Transactions
GROUP by Flight, DepDate, DaystoDeparture
ORDER by Flight, DepDate desc, DaystoDeparture desc

I added only() because it was giving me a syntax error, So i assumed that rangesum() was the issue.
when I look at the resulting loaded table. Total_Curve always equals Sum(Sold), so it looks like my Peek('Total_Curve') is not returning anything?
I also tried without the '' around Total_Curve and same result.

Any help/advice appreciated,
Bernard

Acculumation in Load Script

Oleg_Troyansky — Fri, 18 Sep 2009 02:03:51 GMT

I think the problem is related to the fact that you are trying to accumulate values in the same load statement that's also aggregating using "Group BY".

The "Group By" syntax is quite demanding - you can only list key fields and aggregated functions there (hence the syntax error).

I think you need to aggregate the data first, and then reload it again using "Resident" load, to calculate your accumulated total.

A few things raise questions:

- what are you trying to do with sum(Previous(Sold)) ? I'm not sure what kind of results you might get from this function, in combination with the Group BY...

- What numbers are you trying to summarize under Total_Curve?

- Do you want your total to accumulate data across all Flights, or keep separate totals for different Flights?

Bottom line - separate accumulation and aggregation into 2 different statements...

cheers!

Acculumation in Load Script

Fri, 18 Sep 2009 03:16:51 GMT

Thanks, I think i got it.