topic Re: How to make table like this? in QlikView

How to make table like this?

Mon, 05 Oct 2015 10:02:52 GMT

Dear All Master Qlik,

I need all of your help to create this table :

Date

Start Stock

OUT

End Stock

01/05/2014

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.

Re: How to make table like this?

Peter_Cammaert — Mon, 05 Oct 2015 12:00:22 GMT

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...)

Re: How to make table like this?

sunilkumarqv — Mon, 05 Oct 2015 12:00:38 GMT

For start Stock required clarification

Re: How to make table like this?

Tue, 06 Oct 2015 02:04:51 GMT

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

Re: How to make table like this?

Tue, 06 Oct 2015 02:09:13 GMT

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.

Re: How to make table like this?

Peter_Cammaert — Tue, 06 Oct 2015 07:30:34 GMT

Hi Hadi Hartono,

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

Best,

Peter

Re: How to make table like this?

SreeniJD — Tue, 06 Oct 2015 07:34:07 GMT

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

Re: How to make table like this?

Kushal_Chawda — Tue, 06 Oct 2015 07:36:57 GMT

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

Re: How to make table like this?

Tue, 06 Oct 2015 07:38:41 GMT

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

Re: How to make table like this?

Tue, 06 Oct 2015 07:41:06 GMT

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.

Re: How to make table like this?

Tue, 06 Oct 2015 07:46:40 GMT

Dear Mr Kush141087,

Like Mr Jd Sreeni said, it was date of current month, so actually it was month periodic stock not day periodic stock.

Thanks for your concern.

Regard's,

Hadi.

Re: How to make table like this?

priyarane — Tue, 06 Oct 2015 08:48:24 GMT

Re: How to make table like this?

Tue, 06 Oct 2015 09:32:40 GMT

Dear Priya,

Your example is exactly what i mean. how you do that? can you help me with your example.

Many thanks if you could give me your example...

Regard's,

Hadi.

Re: How to make table like this?

priyarane — Tue, 06 Oct 2015 10:10:10 GMT

Hi Hadi,

You can check in my QVW.

Re: How to make table like this?

Peter_Cammaert — Tue, 06 Oct 2015 10:45:00 GMT

Here with some example data. Does it work ok?

Best,

Peter

Re: How to make table like this?

Wed, 07 Oct 2015 01:05:45 GMT

Dear Priya,

How i can check your QVW? I'm still newbie in this forum, can you explain more detail?

So many thanks..

Regard's,

Hadi

Re: How to make table like this?

Wed, 07 Oct 2015 01:07:28 GMT

Dear Peter,

It does OK, but can you show your QVW ?

So many thanks to you...

Regard's,

Hadi

Re: How to make table like this?

Peter_Cammaert — Wed, 07 Oct 2015 08:55:54 GMT

The QVW was attached to my previous post from the beginning. However, you won't see attachments if you follow this thread via email, mobile phone or in your Inbox.

Open a web browser and navigate to the original thread How to make table like this?

You'll see that Sunil, Priya and myself all have documents attached to their posts. Click on any such attachment, and your browser will start a download of the file.

Note that to open these documents, you'll need a QV Desktop license. That's why we usually post scripts and expressions in this thread as well.

Peter

Re: How to make table like this?

Wed, 07 Oct 2015 09:05:20 GMT

Dear Peter,

Thanks for the explanation, sorry for not know that the file is attach on the post.

So many thanks to you...

Regard's,

Hadi

Re: How to make table like this?

Peter_Cammaert — Wed, 07 Oct 2015 09:08:15 GMT

No problem. Enjoy your time in the Qlik Community.

Best,

Peter