topic Re: How to add all previously loaded values during LOAD? in QlikView

How to add all previously loaded values during LOAD?

Mon, 22 Oct 2012 13:15:26 GMT

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 ?

Re: How to add all previously loaded values during LOAD?

fosuzuki — Mon, 22 Oct 2012 21:22:24 GMT

Hi,

check the attached qvw.

Hope this helps

Fernando

Re: How to add all previously loaded values during LOAD?

Tue, 23 Oct 2012 05:53:43 GMT

Hi,

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

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

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

Re: How to add all previously loaded values during LOAD?

hic — Tue, 23 Oct 2012 06:01:51 GMT

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

Re: How to add all previously loaded values during LOAD?

Tue, 23 Oct 2012 07:40:29 GMT

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

Re: How to add all previously loaded values during LOAD?

Tue, 23 Oct 2012 07:43:43 GMT

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

Re: How to add all previously loaded values during LOAD?

hic — Tue, 23 Oct 2012 08:10:32 GMT

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

Re: How to add all previously loaded values during LOAD?

fosuzuki — Tue, 23 Oct 2012 10:37:13 GMT

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;

DROP Table RawData;

Re: How to add all previously loaded values during LOAD?

Tue, 23 Oct 2012 11:34:37 GMT

Thanks! Works like a charm