Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Re: Regarding Stock First in First Out

Hi Andrew,,

I found there is a small problem. If there are thousands of records, it will take many times to load the data. Could you help to optimize the 'For' sentence? many thanks

Highlighted
chrwolf64
Contributor III

Re: Regarding Stock First in First Out

Hi Jiadong,

You can get the same result without For loops:

// Building the mapping table for quantities.

Max1:

LOAD Max(Qty) as MaxQty1

Resident Purchases;


Max2:

LOAD Max(Qty) as MaxQty2

Resident Sales;


LET vMaxQty=RangeMax(FieldValue('MaxQty1',1),FieldValue('MaxQty2',1));

DROP Tables Max1,Max2;


// Because all quantities in your sample data are a multiple of 5, we can set the base quantity to 5.

// Maybe for your real data you have set it to 1.

set vBaseQty=5;

let vNoR= $(vMaxQty)/$(vBaseQty);


MapQty:

Mapping

LOAD

RecNo()*$(vBaseQty) as MapQty.Old,

Text(Repeat('$(vBaseQty),',RecNo()-1) & '$(vBaseQty)') as MapQty.New

AutoGenerate $(vNoR);


// Map quantities

MAP Qty using MapQty;


Purch1:

NoConcatenate

LOAD * Resident Purchases;


DROP Table Purchases;


Sales1:

NoConcatenate

LOAD * Resident Sales;


DROP Fields StockCode, Qty From Sales;


UNMAP *;


// Split purchase quantities

Purch2:

NoConcatenate

LOAD

InDocument,

StockCode,

SubField(Qty,',') as Qty,

Date

Resident Purch1;


DROP Table Purch1;


// Create ID for the join

FIFO1:

LOAD

InDocument,

StockCode,

if(StockCode=Previous(StockCode),Peek('ID',-1)+1,1) as ID

Resident Purch2

Order By StockCode,Date,InDocument;


DROP Table Purch2;


// Split sales quantities

Sales2:

NoConcatenate

LOAD

OutDocument,

StockCode,

SubField(Qty,',') as Qty,

Date

Resident Sales1;


DROP Table Sales1;


// Join Incoming and Outgoing

Outer Join(FIFO1)

LOAD

OutDocument,

StockCode,

if(StockCode=Previous(StockCode),Peek('ID',-1)+1,1) as ID

Resident Sales2

Order By StockCode,Date,OutDocument;


DROP Table Sales2;


Join(FIFO1)

LOAD * INLINE [

    Qty

    $(vBaseQty)

];


// Final FIFO Table

FIFO:

LOAD

StockCode,

InDocument,

OutDocument,

Sum(Qty) as Qty

Resident FIFO1

Group By StockCode,InDocument,OutDocument;


DROP Table FIFO1;


Left Join (FIFO)

LOAD

OutDocument,

Date

Resident Sales;

DROP Table Sales;

Regards

Christian

Highlighted
Not applicable

Re: Regarding Stock First in First Out

Hi Christian,

Thanks for your help. I tried your script. But there is a little error below.

     

StockCodeInDocumentDateOutDocumentQty
CPAA57PIN0880962011/12/28SIN2083521
CPAA57PIN0880962012/2/6SIN2092404
CPAA57PIN0885952012/2/6SIN2092401
CPAA57PIN0885952012/3/5SIN2099447
CPAA57PIN0885952012/6/5SIN2122225
CPAA57PIN0885952012/7/17SIN2132935
CPAA57PIN0885952012/11/9SIN2160095
CPAA57PIN0885952012/12/11SIN2168585
CPAA57PIN0885952013/1/17SIN2176595
CPAA57PIN0885952013/1/23SIN21780112
CPAA572013/1/23SIN2178018
CPAA99PIN1125932014/12/11SIN23605525
CPAA99PIN1129822014/12/11SIN23605525
CPAA99PIN1137782015/1/13SIN23715150
CPAA99PIN1177892015/5/20SIN24061250
CPAA99PIN11831650

For OutDocument SIN208352, the Qty should be 0.5 not 1.(Pin088096 Qty is 5 and Sin208352 Qty is 4.5)  Could you help to check it?

The following is the script:

Purchases:

LOAD * INLINE [

    InDocument, StockCode, Qty, Date

    PIN088096, CPAA57, 5, 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:

LOAD * INLINE [

    OutDocument, StockCode, Qty, Date

    SIN208352, CPAA57, 4.5, 2011/12/28

    SIN209240, CPAA57, 5, 2012/2/6

    SIN209944, CPAA57, 7, 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

];

// Building the mapping table for quantities.

Max1:

LOAD Max(Qty) as MaxQty1

Resident Purchases;

Max2:

LOAD Max(Qty) as MaxQty2

Resident Sales;

LET vMaxQty=RangeMax(FieldValue('MaxQty1',1),FieldValue('MaxQty2',1));

DROP Tables Max1,Max2;

// Because all quantities in your sample data are a multiple of 5, we can set the base quantity to 5.

// Maybe for your real data you have set it to 1.

set vBaseQty=1;

let vNoR= $(vMaxQty)/$(vBaseQty);

MapQty:

Mapping

LOAD

RecNo()*$(vBaseQty) as MapQty.Old,

Text(Repeat('$(vBaseQty),',RecNo()-1) & '$(vBaseQty)') as MapQty.New

AutoGenerate $(vNoR);

// Map quantities

MAP Qty using MapQty;

Purch1:

NoConcatenate

LOAD * Resident Purchases;

DROP Table Purchases;

Sales1:

NoConcatenate

LOAD * Resident Sales;

DROP Fields StockCode, Qty From Sales;

UNMAP *;

// Split purchase quantities

Purch2:

NoConcatenate

LOAD

InDocument,

StockCode,

SubField(Qty,',') as Qty,

Date

Resident Purch1;

//

DROP Table Purch1;

// Create ID for the join

FIFO1:

LOAD

InDocument,

StockCode,

if(StockCode=Previous(StockCode),Peek('ID',-1)+1,1) as ID

Resident Purch2

Order By StockCode,Date,InDocument;

DROP Table Purch2;

// Split sales quantities

Sales2:

NoConcatenate

LOAD

OutDocument,

StockCode,

SubField(Qty,',') as Qty,

Date

Resident Sales1;

DROP Table Sales1;

// Join Incoming and Outgoing

Outer Join(FIFO1)

LOAD

OutDocument,

StockCode,

if(StockCode=Previous(StockCode),Peek('ID',-1)+1,1) as ID

Resident Sales2

Order By StockCode,Date,OutDocument;

DROP Table Sales2;

Join(FIFO1)

LOAD * INLINE [

    Qty

    $(vBaseQty)

];

// Final FIFO Table

FIFO:

LOAD

StockCode,

InDocument,

OutDocument,

Sum(Qty) as Qty

Resident FIFO1

Group By StockCode,InDocument,OutDocument;

DROP Table FIFO1;

Left Join (FIFO)

LOAD

OutDocument,

Date

Resident Sales;

DROP Table Sales;

Highlighted
chrwolf64
Contributor III

Re: Regarding Stock First in First Out

Hi Jiadong,

you have to set the base quantity depending on the number of decimals.

But this will produce a very high load, so I think that it is not practical for real data.

Try the following script and let me know the result.

set vMappingTables='';

//Loop over StockCode for saveing performance

for i = 1 to FieldValueCount('StockCode')

  let vStockCode=FieldValue('StockCode',i);

  TRACE ****** StockCode: $(vStockCode) ******;

  Qtys:

  LOAD Distinct Qty as Qty§ Resident Purchases Where Match(StockCode,'$(vStockCode)');

  LOAD Distinct Qty as Qty§ Resident Sales Where Match(StockCode,'$(vStockCode)');

  MaxQty:

  LOAD Max(Qty§) as MaxQty

  Resident Qtys;

  LET vMaxQty=FieldValue('MaxQty',1);

  //Determin the number of decimals

  Decimals:

  LOAD Distinct

  SubField(Qty§,'.',2) as Dec

  Resident Qtys;

  DROP Tables Qtys,MaxQty;

  set vNrOfDec=0;

  For j=1 to FieldValueCount('Dec')

  let vNrOfDec=RangeMax($(vNrOfDec),Len(FieldValue('Dec',j)));

  next

  DROP Table Decimals;

  //Set BassQuantity

  let vBaseQty=1/pow(10,$(vNrOfDec));

  TRACE ==> BaseQty: $(vBaseQty);

  //Building Map Table

  set vMapTable='MapQty$(vNrOfDec)';

  if Index('vMappingTables','$(vMapTable),')=0 then

  let vMappingTables = '$(vMappingTables)' & '$(vMapTable),';

  let vNoR= $(vMaxQty)/$(vBaseQty);

  [$(vMapTable)]:

  Mapping

  LOAD

  RecNo()*$(vBaseQty) as $(vMapTable).Old,

  Text(Repeat('1,',RecNo()-1) & '1') as $(vMapTable).New

  AutoGenerate $(vNoR);

  end if

  // Map quantities

  MAP Qty using [$(vMapTable)];

  Purch1:

  NoConcatenate

  LOAD * Resident Purchases Where Match(StockCode,'$(vStockCode)');

  Sales1:

  NoConcatenate

  LOAD * Resident Sales Where Match(StockCode,'$(vStockCode)');

  UNMAP *;

  // Split purchase quantities

  Purch2:

  NoConcatenate

  LOAD

  InDocument,

  StockCode,

  SubField(Qty,',') as Qty,

  Date

  Resident Purch1;

  DROP Table Purch1;

  // Create ID for the join

  FIFO1:

  LOAD

  InDocument,

  StockCode,

  if(StockCode=Previous(StockCode),Peek('ID',-1)+1,1) as ID

  Resident Purch2

  Order By StockCode,Date,InDocument;

  DROP Table Purch2;

  // Split sales quantities

  Sales2:

  NoConcatenate

  LOAD

  OutDocument,

  StockCode,

  SubField(Qty,',') as Qty,

  Date

  Resident Sales1;

  DROP Table Sales1;

  // Join Incoming and Outgoing

  Outer Join(FIFO1)

  LOAD

  OutDocument,

  StockCode,

  if(StockCode=Previous(StockCode),Peek('ID',-1)+1,1) as ID

  Resident Sales2

  Order By StockCode,Date,OutDocument;

  DROP Table Sales2;

  Join(FIFO1)

  LOAD * INLINE [

     StockCode,Qty

     $(vStockCode),$(vBaseQty)

  ];

  // Final FIFO Table

  FIFO:

  LOAD

  StockCode,

  InDocument,

  OutDocument,

  If(IsNull(InDocument),Qty*-1,Qty) as Qty

  ;

  LOAD

  StockCode,

  InDocument,

  OutDocument,

  Sum(Qty) as Qty

  Resident FIFO1

  Group By StockCode,InDocument,OutDocument;

  DROP Table FIFO1;

next

Left Join (FIFO)

LOAD

OutDocument,

Date

Resident Sales;

RENAME Table Sales to Sales§;

Sales:

LOAD Distinct

OutDocument,

Qty as OutQty

Resident Sales§;

DROP Table Sales§;

RENAME Table Purchases to Purchases§;

Purchases:

LOAD Distinct

InDocument,

Qty as InQty

Resident Purchases§;

DROP Table Purchases§;

Regards

Christian

Highlighted
Not applicable

Re: Regarding Stock First in First Out

Hi Christian,

It still produce a very high load. I took a all morning to run this script. Could you help to optimize it? Many thanks.

Highlighted
effinty2112
Honored Contributor

Re: Regarding Stock First in First Out

Hi Chen,

                    Here's a different approach:

Purchases:

Load

*,

if([Stock Code] = Peek('Stock Code'),Rangesum(Peek(ToPurch), 1),1) as FromPurch,

if([Stock Code] = Peek('Stock Code'),Rangesum(Peek(ToPurch), PurchaseQty),PurchaseQty) as ToPurch;

LOAD * INLINE [

    In Document, Stock Code, PurchaseQty, 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

];

Drop Field Date;

Sales:

Load

*,

if([Stock Code] = Peek('Stock Code'),Rangesum(Peek(ToSell), 1),1) as FromSell,

if([Stock Code] = Peek('Stock Code'),Rangesum(Peek(ToSell), Qty),Qty) as ToSell;

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

    ];

Left Join(Sales)

LOAD * Resident Purchases;

NoConcatenate

Sales1:

LOAD * Resident Sales Where

FromSell <= ToPurch AND ToSell >= FromPurch;

Drop Table Sales;

NoConcatenate

Sales:

Load

[Stock Code],

[In Document],

[Out Document],

if(Peek('Out Document') = [Out Document],

RangeMin(PurchaseQty,Qty - Peek('ToPurch')),

RangeMin(PurchaseQty,Qty)) as Qty,

Date

Resident Sales1;

drop Table Sales1;

//Unsold

AggrSales:

LOAD

[Stock Code],

Sum(Qty) as TotalQty

Resident Sales Group by [Stock Code];

Left Join(Purchases)

LOAD * Resident AggrSales;

DROP table AggrSales;

Unsold:

LOAD

[Stock Code],

[In Document],

ToPurch - TotalQty as Unsold

Resident Purchases Where ToPurch > TotalQty;

Drop Table Purchases;

Concatenate(Sales)

LOAD * Resident Unsold;

DROP Table Unsold;

Gives this table:

Stock Code In Document Out Document Qty Date Unsold
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

Cheers

Andrew

Highlighted
Not applicable

Re: Regarding Stock First in First Out

Hi Andrew,

Thanks so much. But I have a small question below.

When I changed the sales qty like below:

Purchase:

LOAD * INLINE [

    In Document, Stock Code, PurchaseQty, Date

    PIN088096, CPAA57, 20, 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:

LOAD * INLINE [

    Out Document, Stock Code, Qty, Date

    SIN208352, CPAA57, 5.52, 2011/12/28   (change the Qty from 5 to 5.52)

    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

    ];

The result below:

      

Stock CodeDateIn DocumentOut DocumentQtyUnsold
CPAA572011/12/28PIN088096SIN2083525.52
CPAA572012/2/6PIN088096SIN2092405.00
CPAA572012/3/5PIN088096SIN2099445.00
CPAA572012/6/5PIN088096SIN2122225.00
CPAA572012/7/17PIN088595SIN2132935.00
CPAA572012/11/9PIN088595SIN2160095.00
CPAA572012/12/11PIN088595SIN2168585.00
CPAA572013/1/17PIN088595SIN2176595.00
CPAA572013/1/23PIN088595SIN21780120.00
CPAA57PIN088595    4.48
CPAA992014/12/11PIN112593SIN23605525.00
CPAA992014/12/11PIN112982SIN23605525.00
CPAA992015/1/13PIN113778SIN23715150.00
CPAA992015/5/20PIN117789SIN24061250.00
CPAA99PIN11831650

For the QTY of Out Document SIN212222,  it should be 4,48

And the rest Qty 0.52 should be sold from next In Document  PIN088595  like below


CPAA57   2012/7/17  PIN088595  SIN212222  0.52.

Could you help it?