How to make table like this?

[#1]

Dear All Master Qlik,

I need all of your help to create this table :

Date	Start Stock	IN	OUT	End Stock
01/05/2014	0	235.413	-30.469	204.944

and that upper table have row data like this :

Movement Type	Date	Total
IN	01/05/2014	235.413
OUT	01/05/2014	-30.469

I need to make a column with name "Start Stock" and "End Stock".

Please help me, and thanks for your concern and attention.

Regard's,

Hadi.

[Peter_Cammaert]

I am assuming that you want this to be created in the Load script, correct?

If these are the only fields in your input table (no product ID, no storage location, just an aggregation of movements by date) then you may do something like this (let's call the basic table RawData😞

ExtendedRawData:

NOCONCATENATE

LOAD [Movement Type],

     MonthStart(Date) AS Date,

     Total

RESIDENT RawData;

BasicINOUT:

NOCONCATENATE

LOAD Sum(Total) AS IN, Date

RESIDENT ExtendedRawData

WHERE Movement = 'IN'

GROUP BY Date;

JOIN (BasicINOUT)

LOAD Sum(Total) AS OUT, Date

RESIDENT ExtendedRawData

WHERE Movement = 'OUT'

GROUP BY Date;

DROP Table ExtendedRawData;

SortedBasicINOUT:

NOCONCATENATE

LOAD Alt(IN, 0) AS IN, Alt(OUT, 0) AS OUT, Date

RESIDENT BasicINOUT

ORDER BY Date ASC;

Drop Table BasicINOUT;

StockMovements:

LOAD IF (RowNo() = 1, 0, peek('End Stock')) AS [Start Stock],

     IN, OUT,

     IF (RowNo() = 1, 0, peek('End Stock')) + IN + OUT AS [End Stock]

RESIDENT SortedBasicINOUT;

DROP Table SortedBasicINOUT;

Best,

Peter

[Edit]: Added some preliminary code to group stock movements by month, as this seems to be your requirement (wasn't really clear from your OP but I should have known this...)

[sunilkumarqv]

For start Stock required clarification

[#4]

Dear Peter,

Thanks for your help, but i must say that what you suggest is not working.

i dont know why not working but there is some error when i load the script you gave me.

but i aprreciate your efffort to help me, many thanks..

Regard's,

Hadi

[#5]

Dear Mr. Sunil Kumar,

What do you mean by required clarification?? What suppose write as start stock in the table is end stock at 1 month before. i write down 0 for assume that is the first month so the stock is zero.

[Peter_Cammaert]

Hi Hadi Hartono,

I edited my original example a bit so that it aggregates stock movements by month instead of by day.

Best,

Peter

[SreeniJD]

Hi Hadi,

Your end stock of previous month should be start stock of current month right...

for 01/06/2014, your stock should be 204.944

This can be achieved with Rangesum and above functions...

HTH

Sreeni

[Kushal_Chawda]

what will be the start of the stock for 02/05/2015?

[#9]

Dear Mr Jd Sreeni,

if i just want to get the value maybe your answer was correct. But i want to make a table with field start stock, IN, OUT, End stock. I think it's a bit different.

But thank for your concern and i really appreciated.

Regard's,

Hadi

[#10]

Dear Mr Peter,

Could you give some complete example please, so i can more understanding what you mean to solving my problem. So many thanks.

Regard's,

Hadi.