FIFO data join

[MindaugasBacius]

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!

[MK_QSL]

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

[MindaugasBacius]

The quantity in every single row is 1 piece.

[MindaugasBacius · Accepted solution]

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

Purchase:

LOAD * INLINE [

    Date, Product, Price

    2015-01-01, A, 1

    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

    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: