Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to make table like this?

Dear All Master Qlik,

I need all of your help to create this table :

 

DateStart StockINOUTEnd Stock
01/05/20140235.413-30.469
204.944

and that upper table have row data like this :

  

Movement TypeDateTotal
IN01/05/2014235.413
OUT01/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.

1 Solution

Accepted Solutions
Peter_Cammaert

Here with some example data. Does it work ok?

Stock Overview thread 185431s.jpg

Best,

Peter

View solution in original post

20 Replies
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
Specialist II
Specialist II

For start Stock required clarification

Not applicable
Author

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

Not applicable
Author

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
Specialist
Specialist

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

Kush
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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.