Discussion Board for collaboration related to QlikView App Development.
Dear All Master Qlik,
I need all of your help to create this table :
|Date||Start Stock||IN||OUT||End Stock|
and that upper table have row data like this :
|Movement Type||Date||Total |
I need to make a column with name "Start Stock" and "End Stock".
Please help me, and thanks for your concern and attention.
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😞
LOAD [Movement Type],
MonthStart(Date) AS Date,
LOAD Sum(Total) AS IN, Date
WHERE Movement = 'IN'
GROUP BY Date;
LOAD Sum(Total) AS OUT, Date
WHERE Movement = 'OUT'
GROUP BY Date;
DROP Table ExtendedRawData;
LOAD Alt(IN, 0) AS IN, Alt(OUT, 0) AS OUT, Date
ORDER BY Date ASC;
Drop Table BasicINOUT;
LOAD IF (RowNo() = 1, 0, peek('End Stock')) AS [Start Stock],
IF (RowNo() = 1, 0, peek('End Stock')) + IN + OUT AS [End Stock]
DROP Table SortedBasicINOUT;
[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...)
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..
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.
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...
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.