topic Re: running total by load in QlikView

running total by load

Anonymous — Mon, 18 May 2015 06:43:46 GMT

Hi friends,

I have a load query,

(I already have a table 'stock' loaded from oracle and I want to make it cumulative sum with a load query,

my table is like that :

unit_id stock_term stock_value

1 201401 10

1 201402 30

1 201403 20

2 201404 40

2 201405 20

2 201406 40

ı want to convert it to :

(ordered by stock_term)

unit_id stock_term cumulative_stock_value

1 201401 10

1 201402 40

1 201403 60

2 201404 40

2 201405 60

2 201406 100

this below statement is false,

what is the mistake that I make?

Join(stock)

LOAD unit_id, stock_term,

Sum(if(stock_status=0,stock_value)) + RangeSum(Alt(Peek('Cumulative_Stock'), 0))

as Cumulative_Stock

Resident stock

Group By unit_id, stock_term

Order By stock_term;

Re: running total by load

jonathandienst — Mon, 18 May 2015 06:56:19 GMT

Sum(if(stock_status=0, stock_value)) + If(unit_id = Previous(unit_id), Alt(Peek(Cumulative_Stock), 0)) as Cumulative_Stock

Re: running total by load

Anonymous — Mon, 18 May 2015 06:58:59 GMT

thanks, but my unit ID s are random in the table (like 1,5,3,7,3,8,11)

they are not ordered,

Re: running total by load

senpradip007 — Mon, 18 May 2015 07:01:52 GMT

stock_status filed is not there in your original load statement.

Re: running total by load

Anonymous — Mon, 18 May 2015 07:03:22 GMT

yes but I already have it in my original table, nothing changes...

Re: running total by load

jonathandienst — Mon, 18 May 2015 07:03:48 GMT

Join(stock)

LOAD unit_id,

stock_term,

Sum(if(stock_status=0, stock_value)) + If(unit_id = Previous(unit_id), Alt(Peek(Cumulative_Stock), 0)) as Cumulative_Stock

Resident stock

Group By unit_id, stock_term

Order By unit_id, stock_term

Re: running total by load

senpradip007 — Mon, 18 May 2015 07:11:39 GMT

Have a look at this app, hope it will help.

Re: running total by load

Anonymous — Mon, 18 May 2015 07:24:45 GMT

this returns NULL

Re: running total by load

senpradip007 — Mon, 18 May 2015 07:27:55 GMT

Try with

Stock:

LOAD *, if(unit_id<>Peek(unit_id), stock_value, stock_value + peek(Cumulative_Stock)) as Cumulative_Stock Inline [

unit_id, stock_term, stock_value

1, 201401, 10

1, 201402, 30

1, 201403, 20

2, 201404, 40

2, 201405, 20

2, 201406, 40

];

or you can go through the below attachment

Re: running total by load

Mon, 18 May 2015 08:03:07 GMT

Hi,

Try this code:

Test:

LOAD * INLINE [

unit_id, Stock_term, Stock_value

1, 201401, 10

1, 201402, 30

1, 201403, 20

2, 201404, 40

2, 201405, 20

2, 201406, 40

];

NoConcatenate

Test3:

LOAD *,if(unit_id=previous(unit_id),Stock_value +peek('Cumulative Sum'),Stock_value) AS [Cumulative Sum] Resident Test Order BY unit_id,Stock_term asc;

Drop Table Test;

Re: running total by load

Anonymous — Mon, 18 May 2015 08:20:49 GMT

thanks but none of them solved the problem, all have errors, return null etc...

Re: running total by load

jonathandienst — Mon, 18 May 2015 08:24:24 GMT

fisland92 wrote:

this returns NULL

Not sure what that means. You will need to be a bit more specific. Test the LOAD statement without the join. Check that the values of stock_status are what you are expecting - at least one record should have a stock_status of 0 for every unit_id.