Cumulative Sale Grouped by

[#1]

Hi all,

I know this is a recursive issue, but after looking for answers I haven't found anything helpful at all.

I'm loading a table with several fields from my qvd.

After loading it, I would like to add a new column, showing a cumulative cost (or sale, a numeric value afterwards).

But first, I'm looking to group this table by some of my fields.

So, it should look this way:

Entity     Date          Sale     Acum

1           01/01/2011     15          15

1           02/02/2011     30          45

2          05/03/2011      50          50

2          07/04/2011     100        150

This is the script I've been working on:

LOAD

*    

FROM

[MyQVD.QVD]

(qvd);

//I use Join and resident statements to apply my changes directly in my recently loaded table.

Join Load *

Resident FSDR order by Entity, Date;

//I've been trying two different ways to load the new column Acum, last one is the previous and commented one:

Load *,

//if(peek('Entity',-1)<> Entity,Sale,Sale + peek('Acum',-1)) as Acum

sum(Sale) as Acum

resident FSDR group by Entity, Date;

I've been reading the reference manual trying to find answers, and also surfing into the Community posts. I haven't been clever enough to make it work. If I use sum(sale), says me "Invalid Function" and if I use peek, it says I need an aggregation function to use a group by clause.

Thanks for reading first of all, and also for your help in advance.

Kind regards,

Aitor.

[#2]

Hi,

Can you try it like this, it worked as it should without any aggregation when using the Peek:

t1:

//replace with your qvd
LOAD * Inline [
Entity, Date, Sale
1,01/01/2011,15
1,02/02/2011,30
2,05/03/2011,50
2,07/04/2011,100
];

Join //replace with your joining table without sort clause

LOAD * Inline [
Entity, Date, Sale
4,01/12/2011,10
4,01/05/2011,15
2,01/01/2012,70
1,01/20/2011,10
3,01/15/2011,20
];

t2: //ensure the data is sorted as needed after the join
NoConcatenate
LOAD *
Resident t1
Order By Entity, Date;

DROP Table t1;

t3:
LOAD Entity,
Date,
Sale,
if(Peek('Entity',-1)<>Entity, Sale, Sale + Peek('Acum',-1)) as Acum
Resident t2;

DROP Table t2;

Hope it helps.