Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

CHALLENGE! How to keep track of stock per lot without lot :-)

Dear all,

I have a very cool challenge which is easy to work out in Excel but (for me) really difficult in QV. Please find attached Excel file for Input & requested Output (= Stock). The Stock column is the expected outcome per day based on the records from the Input.

The challenge is to keep track of a LotNr once there are multiple LotNrs on an item location. Based on First/Last In First Out (FIFO/LIFO) the Stock is tracked.

Description may be a bit vague but once you see the Excel it will make sense (or please ask me and I will explain in further detail).

Hope one of you can help me on the way...

Best regards,

Marcel

Tags (1)
6 Replies
MVP
MVP

Re: CHALLENGE! How to keep track of stock per lot without lot :-)

The Result provided for 03-Jan looks wrong. Please check and let me know.

Not applicable

Re: CHALLENGE! How to keep track of stock per lot without lot :-)

Hi Manish,

No it's okay:

Transaction:

Jan 1: +900 IN

STOCK: +900 IN

Jan 2: -400 IN

Jan 2: +400 A

STOCK: 500 IN, 400 A

Jan 3: -500 IN (therefore Stock IN = 0)

Jan 3: +500 B

STOCK: 0 IN (no show), 400 A, 500 B -- all with LotNr 20150101

MVP
MVP

Re: CHALLENGE! How to keep track of stock per lot without lot :-)

Check LotNr  I mentioned below... Are they Ok?

How come LotNr for A on 02-Jan is 20150101? It should be 20150102

How come LotNr for B on 03-Jan is 20150101? It should be 20150103

Let me know if I am wrong...

                                                          

02-JanESINVLA1863.001.0620150101500
ESAVLA1863.001.0620150102400
03-JanESAVLA1863.001.0620150102400
ESBVLA1863.001.0620150103500
Not applicable

Re: CHALLENGE! How to keep track of stock per lot without lot :-)

Hi Manish,

No, that is the trick: once an item is received for the first time in the warehouse, then I would like to add the LotNr (which officially does not exist). This LotNr should taggle along all item movements. The ultimate goal is to measure stock age per item location (based on assumption of FIFO and LIFO).

Hope this clarifies my question?

Thank you for your interest in this challenge!

Regards,

Marcel

Not applicable

Re: CHALLENGE! How to keep track of stock per lot without lot :-)

I have more solution areas, each having its own difficulties. I find it difficult to determine best approach.

Solution 1: add the correct "LotNr" to every record. Then the only thing I need to do is to sum the Qty to get the stock per item location lot.

Tricky part:

- How to split up such a record when a movement comprises more than 1 lot? Then FIFO/LIFO assumption also arises (which Lot to pick first).

Solution 2: determine LotNr after calculating stock.

Here I mean that you calculate all transactions per Item & Date (Inflow), e.g. on Jan 1 and 10 there was a new inflow. Than subtract all outflow and determine (again from FIFO and LIFO perspective) which LotNrs are left with what Qty.

It is relatively easy to explain when using the idea of buckets where you decide for each record what to do:

1. get it from stock (scenario: sufficient stock)

2. split rule to match LotNr Qty (obviously you cannot have negative stock per lot)

Quite a challenge (at least in my opinion)...

Not applicable

Re: CHALLENGE! How to keep track of stock per lot without lot :-)

One more thing to add: it is possible to alter the input file for easing scripting. But this is how I now receive the data from the item transaction table..

Community Browser