How to add all previously loaded values during LOAD?

[#1]

Hello everyone!

I have data structure like this:

Item	Date	Sales qty
item1	2012.10.19	1
item1	2012.10.20	2
item1	2012.10.21	3
item2	2012.10.19	1
item2	2012.10.19	2
item2	2012.10.20	3
item2	2012.10.21	4
item3	2012.10.19	1
item3	2012.10.20	2
item3	2012.10.21	3

I need to get sum of sales for all previos dates for each Date and for each Item

Result must by like:

Item	2012.10.20	2012.10.20	2012.10.21
item1	1	3	6
item2	3	6	10
item3	1	3	6

In chart I achive this using: =sum({Date={"<=$(vMaxDate)"}>} [Sales qty]) where vMaxDate=max(Date)

I want to do this during LOAD - maybe someone could help me ?

[fosuzuki]

Hi,

check the attached qvw.

Hope this helps

Fernando

[#3]

Hi,

you should not do this, because you destroy your clean data structure.

This is not a full solution, but maybe a hint.

A:

LOAD * INLINE [ item ];

for each myDate in '2012.10.19', '2012.10.20', '2012.10.21'

     Join (A)

     LOAD

          item,

          sum( sales qty ) as myDate

     FROM

          source

     WHERE

          Date = myDate

     GROUP BY

          item;

next myDate

[hic]

I would instead go for the following lines:

Data2:

Load Item, Date, Sum([Sales qty]) as [Sales Qty]

From RawData

          Group By Item, Date;

Data:

Load Item, Date,

          if(Item=peek(Item),NumSum([Sales Qty],peek([Sales Qty])),[Sales Qty]) as AccumulatedSales,

          [Sales Qty]

resident Data2

          Order By Item, Date;

Drop Table Data2;

HIC

[#5]

Thanks Fernando for your reply, but I am using personal edition Maybe you can explain your solution to my problem, or post scrip here ?

[#6]

Thanks Henric, but your solution do not do what I neet, I need to add all previos sales not only form previos day ...

[hic · Accepted solution]

Sorry, I missed that. But it is still easy: Use the following to define AccumulatedSales:

if(Item=peek(Item),NumSum([Sales Qty],peek(AccumulatedSales)),[Sales Qty]) as AccumulatedSales,

HIC

[fosuzuki]

Hi Gatis,

here goes my script. (I modified it a little bit because I had missed the requirement for accumulation...)

RawData:

LOAD * Inline [

Item, Date, Sales qty

item1, 2012.10.19, 1

item1, 2012.10.20, 2

item1, 2012.10.21, 3

item2, 2012.10.19, 1

item2, 2012.10.19, 2

item2, 2012.10.20, 3

item2, 2012.10.21, 4

item3, 2012.10.19, 1

item3, 2012.10.20, 2

item3, 2012.10.21, 3

];

let vNoOfDates = FieldValueCount('Date');

NewData:

NoConcatenate

LOAD Distinct Item

Resident RawData;

for i = 1 to $(vNoOfDates)

          LET vDate = FieldValue('Date', i);

          Left Join(NewData)

          LOAD Item,

                     sum([Sales qty]) AS [$(vDate)]

          Resident RawData

          Where Date <= '$(vDate)'

          Group By Item;

next

DROP Table RawData;

[#9]

Thanks! Works like a charm