Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Oliver1
Contributor III
Contributor III

Calculate Inventory with Inventory Movements

Hello everybody,

I have a table with the actual stock from only one specific day and a table with all movements before this day, so +/- inventory movements for each article. 

Now i want to calculate the stock level for each day and save it in a separate inventory table or add all the stock levels for each day to the allready existing table with the data only form this one day, is there a possibility to do this in the load script?

 

Thank you and br,

Oliver

Labels (1)
2 Replies
caiogil1
Contributor III
Contributor III

Hi @Oliver1 ,

It will be easier to help you if you provide some data sample.

But see if this result in image (attachment) from a data raw sample will help you. It creates the position of Stock at each day since the start (min date from data source) to the last date (max date from data source), considering the movements (IN or OUT) of the Stock. Also I attached the .qvf to you take a look in the ETL used, I recommend you to do this at this stage to not make your Qlik App heavy with front end calculations.

Obs.: date format is 'DD/MM/YYYY'

I hope to learn a lot here and help if I can
Oliver1
Contributor III
Contributor III
Author

Hi @caiogil1 ,

thank you very much, there is just one thing, but this is my fault, here are some example tables:

 

I have the inventory for all articles only for 25.05.2020:

location_iditem_idinventory_datequantity
001A25.05.2020100
001B25.05.202090
001C25.05.202080
002A25.05.202050

 

And I have movements for everyday from 01.01.2019 until 25.05.2020:

location_iditem_idinventory_datedirectionquantity
001A24.05.2020out20
001A24.05.2020in30
001B24.05.2020out50
002A24.05.2020in10

 

Now I want to add the inventory_quantity for each day in the first table.

So exactly what you have allready posted, just the first table is missing.

 

Thank you and br,

Oliver