topic Re: FIFO data join in QlikView

FIFO data join

MindaugasBacius — Thu, 13 Apr 2017 05:46:41 GMT

Hello everybody,

I need an idea how to work this issue.

There are two tables.

Purchase:

Date	Product	Price
2015-01-01	A	1
2015-01-01	A	1
2015-01-03	B	2
2015-01-04	A	3

Sales:

Date	Product
2015-01-02	A
2015-01-04	B
2015-01-04	A
2015-01-04	A

The merged table should look like:

Date	Product	Price
2015-01-02	A	1
2015-01-04	B	2
2015-01-04	A	1
2015-01-04	A	3

The model should work under FIFO principle.

In case you have any clue how should I reach this result - let me now.

Thank you in advance!

Re: FIFO data join

MK_QSL — Thu, 13 Apr 2017 05:54:07 GMT

I think you missed Qty here ! That could also be a part of FIFO.

Re: FIFO data join

MindaugasBacius — Thu, 13 Apr 2017 08:30:17 GMT

The quantity in every single row is 1 piece.

Re: FIFO data join

MindaugasBacius — Fri, 14 Apr 2017 13:52:09 GMT

For this exact issue the solution appears to be quite simple:

Purchase:

LOAD * INLINE [

Date, Product, Price

2015-01-01, A, 1

2015-01-03, B, 2

2015-01-04, A, 3

2015-01-02, C, 2

];

Sales:

LOAD * INLINE [

Date, Product

2015-01-02, A

2015-01-04, B

2015-01-04, A

];

NoConcatenate

tmp:

LOAD *

,AutoNumber(RowNo(), Product) as Number

Resident Sales

Order By Product asc, Date asc

;

Left Join (tmp)

LOAD Price

,Product

,AutoNumber(RowNo(), Product) as Number

Resident Purchase

Order By Product asc, Date asc,

;

DROP Tables Sales, Purchase;

DROP Field Number;

And the outcome is as wanted: