Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Regarding Stock First in First Out

Hi,

I have a question regarding the Stock FIFO below.

I hope the result could be like below:

   

Stock CodeIn DocumentOut DocumentQtyDate
CPAA57PIN088096SIN20835252011/12/28
CPAA57PIN088096SIN20924052012/2/6
CPAA57PIN088096SIN20994452012/3/5
CPAA57PIN088595SIN21222252012/6/5
CPAA57PIN088595SIN21329352012/7/17
CPAA57PIN088595SIN21600952012/11/9
CPAA57PIN088595SIN21685852012/12/11
CPAA57PIN088595SIN21765952013/1/17
CPAA57PIN088595SIN217801202013/1/23
CPAA99PIN112593SIN236055252014/12/11
CPAA99PIN112982SIN236055252014/12/11
CPAA99PIN113778SIN237151502015/1/13
CPAA99PIN117789SIN240612502015/5/20
CPAA99PIN11831650

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

   

Purchases Table
DocumentStock CodeQtyDate
PIN088096CPAA57152011/12/23
PIN088595CPAA57452012/2/15
PIN112593CPAA99252014/11/26
PIN112982CPAA99252014/12/10
PIN113778CPAA99502015/1/16
PIN117789CPAA99502015/6/30
PIN118316CPAA99502015/7/20

 

Sales Table
DocumentStock CodeQtyDate
SIN208352CPAA5752011/12/28
SIN209240CPAA5752012/2/6
SIN209944CPAA5752012/3/5
SIN212222CPAA5752012/6/5
SIN213293CPAA5752012/7/17
SIN216009CPAA5752012/11/9
SIN216858CPAA5752012/12/11
SIN217659CPAA5752013/1/17
SIN217801CPAA57202013/1/23
SIN236055CPAA99502014/12/11
SIN237151CPAA99502015/1/13

SIN240612

CPAA99502015/5/20
16 Replies

Re: Regarding Stock First in First Out

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Regarding Stock First in First Out

Do you want calculate Stock which works in FIFO manner?

Partner
Partner

Re: Regarding Stock First in First Out

Hi

Check these for FIFO in Qlikview.

Creating a FIFO Stock Table

Reg:FIFO QueryReg:FIFO in QlikView

Re: Regarding Stock First in First Out

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;


Not applicable

Re: Regarding Stock First in First Out

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

Not applicable

Re: Regarding Stock First in First Out

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

Not applicable

Re: Regarding Stock First in First Out

I want to calculate stock in FIFO.

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

effinty2112
Honored Contributor

Re: Regarding Stock First in First Out

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
CPAA57PIN088096SIN20835252011/12/28
CPAA57PIN088096SIN20924052012/2/6
CPAA57PIN088096SIN20994452012/3/5
CPAA57PIN088595SIN21222252012/6/5
CPAA57PIN088595SIN21329352012/7/17
CPAA57PIN088595SIN21600952012/11/9
CPAA57PIN088595SIN21685852012/12/11
CPAA57PIN088595SIN21765952013/1/17
CPAA57PIN088595SIN217801202013/1/23
CPAA99PIN112593SIN236055252014/12/11
CPAA99PIN112982SIN236055252014/12/11
CPAA99PIN113778SIN237151502015/1/13
CPAA99PIN117789SIN240612502015/5/20
CPAA99PIN118316 50

Cheers

Andrew

Not applicable

Re: Regarding Stock First in First Out

Hi Andrew,

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