Regarding Stock First in First Out

[#1]

Hi,

I have a question regarding the Stock FIFO below.

I hope the result could be like below:

Stock Code	In Document	Out Document	Qty	Date
CPAA57	PIN088096	SIN208352	5	2011/12/28
CPAA57	PIN088096	SIN209240	5	2012/2/6
CPAA57	PIN088096	SIN209944	5	2012/3/5
CPAA57	PIN088595	SIN212222	5	2012/6/5
CPAA57	PIN088595	SIN213293	5	2012/7/17
CPAA57	PIN088595	SIN216009	5	2012/11/9
CPAA57	PIN088595	SIN216858	5	2012/12/11
CPAA57	PIN088595	SIN217659	5	2013/1/17
CPAA57	PIN088595	SIN217801	20	2013/1/23
CPAA99	PIN112593	SIN236055	25	2014/12/11
CPAA99	PIN112982	SIN236055	25	2014/12/11
CPAA99	PIN113778	SIN237151	50	2015/1/13
CPAA99	PIN117789	SIN240612	50	2015/5/20
CPAA99	PIN118316	50

There are two basice tables below. One is Purchase table, another is Sales Table.

Purchases Table
Document	Stock Code	Qty	Date
PIN088096	CPAA57	15	2011/12/23
PIN088595	CPAA57	45	2012/2/15
PIN112593	CPAA99	25	2014/11/26
PIN112982	CPAA99	25	2014/12/10
PIN113778	CPAA99	50	2015/1/16
PIN117789	CPAA99	50	2015/6/30
PIN118316	CPAA99	50	2015/7/20

Sales Table
Document	Stock Code	Qty	Date
SIN208352	CPAA57	5	2011/12/28
SIN209240	CPAA57	5	2012/2/6
SIN209944	CPAA57	5	2012/3/5
SIN212222	CPAA57	5	2012/6/5
SIN213293	CPAA57	5	2012/7/17
SIN216009	CPAA57	5	2012/11/9
SIN216858	CPAA57	5	2012/12/11
SIN217659	CPAA57	5	2013/1/17
SIN217801	CPAA57	20	2013/1/23
SIN236055	CPAA99	50	2014/12/11
SIN237151	CPAA99	50	2015/1/13
SIN240612	CPAA99	50	2015/5/20

[Anil_Babu_Samineni]

What is the issue? Does this is question / Document.

[shraddha_g]

Do you want calculate Stock which works in FIFO manner?

[yasmeenk]

Hi

Check these for FIFO in Qlikview.

Creating a FIFO Stock Table‌

Reg:FIFO Query‌Reg:FIFO in QlikView‌

[avinashelite]

You want the first table to be generate from the last 2 tables then simple do a join like this

Purchases_Table:

LOAD

Document as [In Document],

Stock Code,

Qty as Purchases_Qty,

Date as Purchases_Date

from

Purchases_Table;

Join (Purchase_table)

LOAD

Document as [Out Document],

Stock Code,

Qty,

Date

from

sales_table;

[#6]

Yes. I want to calculate stock in FIFO. Could you help it?

[#7]

I want to calculate stock in FIFO. Could you help it?

[#8]

I want to calculate stock in FIFO.

The out Document need to match the In Document which is the first one into stock.

[effinty2112]

Hi Chen,

                    This script:

Purchases:

LOAD * INLINE [

    In Document, Stock Code, Qty, Date

    PIN088096, CPAA57, 15, 2011/12/23

    PIN088595, CPAA57, 45, 2012/2/15

    PIN112593, CPAA99, 25, 2014/11/26

    PIN112982, CPAA99, 25, 2014/12/10

    PIN113778, CPAA99, 50, 2015/1/16

    PIN117789, CPAA99, 50, 2015/6/30

    PIN118316, CPAA99, 50, 2015/7/20

];

for i = 1 to NoOfRows('Purchases')

Let vDocument = Peek('In Document',$(i)-1,'Purchases');

Let vStockCode = Peek('Stock Code',$(i)-1,'Purchases');

Let vQty = Peek('Qty',$(i)-1,'Purchases');

PurchaseMatchTable:

LOAD

'$(vDocument)' as [In Document],

'$(vStockCode)' as [Stock Code],

if('$(vStockCode)' = Peek('Stock Code'),Rangesum(Peek(Index), 1),1) as Index

AutoGenerate $(vQty);

Next i;

Sales:

LOAD * INLINE [

    Out Document, Stock Code, Qty, Date

    SIN208352, CPAA57, 5, 2011/12/28

    SIN209240, CPAA57, 5, 2012/2/6

    SIN209944, CPAA57, 5, 2012/3/5

    SIN212222, CPAA57, 5, 2012/6/5

    SIN213293, CPAA57, 5, 2012/7/17

    SIN216009, CPAA57, 5, 2012/11/9

    SIN216858, CPAA57, 5, 2012/12/11

    SIN217659, CPAA57, 5, 2013/1/17

    SIN217801, CPAA57, 20, 2013/1/23

    SIN236055, CPAA99, 50, 2014/12/11

    SIN237151, CPAA99, 50, 2015/1/13

    SIN240612, CPAA99, 50, 2015/5/20

];

for i = 1 to NoOfRows('Sales')

Let vDocument = Peek('Out Document',$(i)-1,'Sales');

Let vStockCode = Peek('Stock Code',$(i)-1,'Sales');

Let vQty = Peek('Qty',$(i)-1,'Sales');

MatchTable:

LOAD

*,

[Stock Code] & '|' & Index as SalesID;

LOAD

'$(vDocument)' as [Out Document],

'$(vStockCode)' as [Stock Code],

if('$(vStockCode)' = Peek('Stock Code'),Rangesum(Peek(Index), 1),1) as Index

AutoGenerate $(vQty);

Next i;

Left Join(MatchTable)

LOAD * Resident PurchaseMatchTable;

RemainingStock:

LOAD

[In Document],

[Stock Code],

Count(Index) as Qty

Resident PurchaseMatchTable

Where not Exists(SalesID,[Stock Code] & '|' & Index)

Group by [In Document],[Stock Code];

Left Join(Sales)

LOAD * Resident MatchTable;

DROP Table MatchTable;

[Final Table]:

LOAD

[Stock Code],

[In Document],

[Out Document],

Count(Qty) as Qty,

Min(Date) as Date

Resident Sales Group by [Stock Code], [In Document],[Out Document];

DROP Table Sales;

Drop Table Purchases;

DROP Table PurchaseMatchTable;

Concatenate([Final Table])

LOAD * Resident RemainingStock;

Drop Table RemainingStock;

Gives you this table:

Stock Code	In Document	Out Document	Qty	Date
CPAA57	PIN088096	SIN208352	5	2011/12/28
CPAA57	PIN088096	SIN209240	5	2012/2/6
CPAA57	PIN088096	SIN209944	5	2012/3/5
CPAA57	PIN088595	SIN212222	5	2012/6/5
CPAA57	PIN088595	SIN213293	5	2012/7/17
CPAA57	PIN088595	SIN216009	5	2012/11/9
CPAA57	PIN088595	SIN216858	5	2012/12/11
CPAA57	PIN088595	SIN217659	5	2013/1/17
CPAA57	PIN088595	SIN217801	20	2013/1/23
CPAA99	PIN112593	SIN236055	25	2014/12/11
CPAA99	PIN112982	SIN236055	25	2014/12/11
CPAA99	PIN113778	SIN237151	50	2015/1/13
CPAA99	PIN117789	SIN240612	50	2015/5/20
CPAA99	PIN118316		50

Cheers

Andrew

[#10]

Hi Andrew,

I tried your script. It works fine. Many thanks.@