The task is to create a dynamic report in Qlik which shows the stock of products at a specific time (selectable by a calendar/date picker). I found several similar community posts for this topic but none of them fits our problem very well.
So, we do have 50 franchisers. Everyone of them has - on average - 10 Stores. The time period we want to evaluate is max. 3 years (start: '01-01-' + current year -2 ).
There are two main tables which we need to use.
Journal/Ledger Table which contains:
ID of every movement of products
Franchiser ID
Store ID
Product ID
...
Stock of Product
type of stock change
stock change
Booking date
Stock History which contains:
Product ID
Stock valid from
Stock valid to
reserved Stock
advised stock
Point 4 & 5 (Stock History) are crucial for our report because they have to be added to the Stock of Product from the Journal/Ledger Value of the stock.
One of the biggest problems is that some products don't have a movement over several days - and therefor no entry in the Journal.
Lets say Product A has a stock of 5 at 01.04.2020 Then Product A is sold once at 19.04.2020 => 4 in stock Inbetween these dates there are no changes in the Stock which means selecting a day inbetween is not possible because there is no entry in the calendar for this product for this date.
I saw a post where someone suggested using a table where every product uses a single row per day and per store for the stock of inventory. But: if we would do this we would face a table with up to 3Years*365Days*100.000Products*50Franchisers*10Stores = ~55 BILLION lines.
The first suggestion which could help was to give all the products a start value of stock when they are loaded. So, we need to set the latest Stock of the Bookingdate before 01-01-2019 in our data as the current stock at 01-01-2019. When we have done this we could use something like Peek() or Previous() in the script to generate the stock for everyday. The current stock should always be the stock at the end of the day.
Could anyone give us an example of how this would look like?
The combination of both tables is critical as well, because we would have to use an intervalmatch. The loading process would last for hours.
