Stock movement synthesis in table

[hhajjali]

Hello,

I have the following structure of table to log in the stock movement:

MVT	ITEM	STOCK	MVT DATE
IN	ITEM 1	STOCK 1	01/01/2017
IN	ITEM 2	STOCK 1	01/01/2017
IN	ITEM 3	STOCK 2	01/01/2017
OUT	ITEM 1	STOCK 1	15/01/2017
IN	ITEM 1	STOCK 2	15/01/2017
IN	ITEM 4	STOCK 1	16/01/2017
IN	ITEM 5	STOCK 1	16/01/2017
IN	ITEM 6	STOCK 2	20/01/2017
OUT	ITEM 5	STOCK 1	28/01/2017

And I need to display a table with the actual Items of my stocks, any idea ?

So results of data above may be as following:

STOCK	ITEM
STOCK 1	ITEM 2
STOCK 1	ITEM 4
STOCK 2	ITEM 3
STOCK 2	ITEM 1
STOCK 2	ITEM 6

Thanks a lot for your help.

[YoussefBelloum]

Hi,

for STOCK1 you have :

ITEM1

ITEM2

ITEM4

ITEM5

how did you filtered the table 2 ?

[hhajjali]

Hello,

In stock1, I have Item 1 goes out of stock 1 at 15/01/2017, and goes in stock 2 at the same date. Also Item 5 goes Out on 28/1/2017.

In the second table (which I am trying to create in qlik sense) I will find only items that are in stock 1, and not all items that passed through. So I may have only : Item 2 and Item 4, which goes IN and never goes out.

And I need to create the table in the application not in load script, so I can by filtering on "MVT DATE" to have an image of the stock at this moment.

Hope it is more clear

[OmarBenSalem · Accepted solution]

Create your new table as follow:

dim 1 : =STOCK

dim 2 : =aggr( if(count(distinct  {<STOCK=p(STOCK)>} MVT)=2,Null(),ITEM),ITEM,STOCK)

and uncheck show null values:

result:

[OmarBenSalem]

hhajjali , did this help u?

[hhajjali]

Hello Omar,

I did not yet have the time to test it, I don't have the qvf on my actual computer. I'll test tomorrow and get back to you. But from the results you have in your comment I'll say yes its will resolve my problem.

Thanks for your help.

[hhajjali]

It resolve my problem, thanks for your help.

For the moment I do not have ITEM that get OUT of a STOCK and get back in later, so the fonction you have provided do correspond to my problem.

But for example if I have: Item 1 that get in in stock 1, than it goes out without going to other stocks (error in stock entry for example) and later on it goes in the stock 1 (manuel error correction), in the table I do not have it. If you have a solution it will be great, other wise I have very few cases so it may not be a problem in the reporting I am creating.

Thanks again.

[hhajjali]

Hello,

I find the solution of multiple in out items in one stock inspired from Omar solution:

DIM1 : = STOCK

DIM2 : = aggr( if(EVEN(count(MOUVEMENT)),Null(),NOM),NOM,FT)

If I have even number of in out, Item will be 100% out of all my stocks, when it is odd, it means it is in 1 stock.

Regards