Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hhajjali
New Contributor III

Stock movement synthesis in table

Hello,

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

   

MVTITEMSTOCKMVT DATE
INITEM 1STOCK 101/01/2017
INITEM 2STOCK 101/01/2017
INITEM 3STOCK 201/01/2017
OUTITEM 1STOCK 115/01/2017
INITEM 1STOCK 215/01/2017
INITEM 4STOCK 116/01/2017
INITEM 5STOCK 116/01/2017
INITEM 6STOCK 220/01/2017
OUTITEM 5STOCK 128/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:

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

Thanks a lot for your help.

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Stock movement synthesis in table

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:

Capture.PNG

result:

Capture.PNG

7 Replies
YoussefBelloum
Esteemed Contributor

Re: Stock movement synthesis in table

Hi,

for STOCK1 you have :

ITEM1

ITEM2

ITEM4

ITEM5


how did you filtered the table 2 ?


hhajjali
New Contributor III

Re: Stock movement synthesis in table

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
Esteemed Contributor

Re: Stock movement synthesis in table

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:

Capture.PNG

result:

Capture.PNG

OmarBenSalem
Esteemed Contributor

Re: Stock movement synthesis in table

hhajjali , did this help u?

hhajjali
New Contributor III

Re: Stock movement synthesis in table

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
New Contributor III

Re: Stock movement synthesis in table

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
New Contributor III

Re: Stock movement synthesis in table

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

Community Browser