topic Re: Create a new table based on date comparison in QlikView

Create a new table based on date comparison

Tue, 16 Feb 2016 10:51:12 GMT

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.

Re: Create a new table based on date comparison

marcus_sommer — Wed, 17 Feb 2016 09:23:51 GMT

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

Re: Create a new table based on date comparison

sunny_talwar — Wed, 17 Feb 2016 09:54:25 GMT

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;

Re: Create a new table based on date comparison

Wed, 17 Feb 2016 10:08:11 GMT

Great, thx!