Re: Regarding Stock First in First Out - Page 2 - Qlik Community

Report Inappropriate Content · ‎2016-10-18

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

Report Inappropriate Content · ‎2016-10-20

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

cwolf · ‎2016-10-21

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

Report Inappropriate Content · ‎2016-10-22

Hi Christian,

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

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

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;

cwolf · ‎2016-10-24

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

Report Inappropriate Content · ‎2016-10-24

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.

effinty2112 · ‎2016-10-25

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
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

Report Inappropriate Content · ‎2016-10-25

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 Code	Date	In Document	Out Document	Qty	Unsold
CPAA57	2011/12/28	PIN088096	SIN208352	5.52
CPAA57	2012/2/6	PIN088096	SIN209240	5.00
CPAA57	2012/3/5	PIN088096	SIN209944	5.00
CPAA57	2012/6/5	PIN088096	SIN212222	5.00
CPAA57	2012/7/17	PIN088595	SIN213293	5.00
CPAA57	2012/11/9	PIN088595	SIN216009	5.00
CPAA57	2012/12/11	PIN088595	SIN216858	5.00
CPAA57	2013/1/17	PIN088595	SIN217659	5.00
CPAA57	2013/1/23	PIN088595	SIN217801	20.00
CPAA57	PIN088595				4.48
CPAA99	2014/12/11	PIN112593	SIN236055	25.00
CPAA99	2014/12/11	PIN112982	SIN236055	25.00
CPAA99	2015/1/13	PIN113778	SIN237151	50.00
CPAA99	2015/5/20	PIN117789	SIN240612	50.00
CPAA99	PIN118316				50

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?