Create a new table based on date comparison

[#1]

Hi,

got two tables,

Sales:

CostPrice:

The CostPricePrSoldVolume for an Item is Valid until an newer date (ValidFrom) for that Item occurs.

Need to find the valid CostPricePrSoldVolume for each Item at the Sales.Date, by comparing ValidFrom and Sales.Date, and multiply CostPricePrSoldVolume with SalesVolume.

Want to create a new table in script, which should look like this:

Item 4 should not appear in the result, because it is not sold.

[marcus_sommer]

You need to extent your costprice-table to an additional column "ValidTo" which you could get from the previous record in an appropriate sorted resident-load per Peek() or Previous() ?. And then you could use IntervalMatch to match these dates to your sales-table.

- Marcus

[sunny_talwar · Accepted solution]

May be this:

Set DateFormat='DD.MM.YYYY';

Sales:

LOAD Item,

  SalesVolume,

  Date(Day) as Date

INLINE [

    Item, SalesVolume, Day

    1, 5, 04.01.2016

    1, 4, 11.01.2016

    1, 7, 01.02.2016

    2, 5, 04.01.2016

    3, 4, 10.01.2016

    3, 5, 16.01.2016

];

CostPrice:

LOAD Item,

  Date(ValidFrom) as ValidFrom,

  CostPricePrSoldVolume 

INLINE [

    Item, ValidFrom, CostPricePrSoldVolume

    1, 01.01.2016, 2

    1, 01.02.2016, "1,5"

    3, 01.01.2016, 4

    3, 15.02.2016, 5

    4, 01.01.2016, 2

];

FinalCostPrice:

LOAD *,

  Date(If(Peek('Item') = Item, Peek('ValidFrom') - 1, Today())) as ValidUntil

Resident CostPrice

Order By Item, ValidFrom desc;

DROP Table CostPrice;

Left Join (Sales)

IntervalMatch (Date, Item)

LOAD ValidFrom,

  ValidUntil,

  Item

Resident FinalCostPrice;

Left Join (Sales)

LOAD *

Resident FinalCostPrice;

DROP Table FinalCostPrice;

FinalTable:

LOAD *,

  SalesVolume * CostPricePrSoldVolume as CostPriceTotal

Resident Sales;

DROP Table Sales;

[#4]

Great, thx!