topic Help with IntervalMatch / Effective Dated records in QlikView

Help with IntervalMatch / Effective Dated records

Tue, 07 Feb 2012 15:17:08 GMT

Hi All - Hoping someone can help me out....I have an effective dated product costing table which I want to marry up with transactional sales data within QV

I've managed to get the load script almost working, but having problems which I beleive is data volume related

So I've got a QVD with the costs, which contains:

ProductID

Cost

Start Date

End Date

This returns about 1.5m records and canont be limited any further. the dates will also never overlap

I then use a calander table with INTERVALMATCH to expand the costing table so it contains every possible date within the Start and End dates

so end result is something like:

ProductID

Cost

Start Date

End Date

ProdctID & Date as FK KEY

I can replicate the [FK KEY] on my sales side, so envison using a ApplyMap with the [FK KEY] to get the Cost value

The calander table has 4 years of dates (about 1460records) and cannot be limited. But the script always fails as its built a 350m+ dataset

Is there a better way of doing something like this? I suspect the problem is INTERVALMATCH

My Script:

//LOAD DATES THE COSTS WILL BE EXPANDED FOR

DATES:

LOAD [DATE-DAY_ID] as DAY_ID

FROM

DATES.qvd

(qvd);

COSTS:

LOAD

PRODUCTID

COST,

START_DATE,

END_DATE;

SELECT SQL......;

//RETAIN COSTS INTO A QVD FILE

STORE COSTS INTO COSTS.QVD;

//EXPAND COSTS FOR EACH PERIOD USING START-END DATES

outer join IntervalMatch ( DAY_ID ) LOAD START_DATE, END_DATE resident COSTS;

COSTS_DATES:

LOAD DAY_ID & '-' & PRODUCTID as [$DATE-PRODUCT],

COST

RESIDENT COSTS

where IsNull(DAY_ID) = 0;

//STORE COST DATA FOR EACH DATA/ITEM

STORE COSTS_DATES INTO COSTS_DATES.QVD;

drop table DATES;

Suggestons welcome.

Thanks, Jay

Help with IntervalMatch / Effective Dated records

disqr_rm — Tue, 07 Feb 2012 22:20:31 GMT

Try this code:

COSTS:

LOAD *,

DAY_ID & '-' & PRODUCTID as [$DATE-PRODUCT];

LOAD

PRODUCTID,

COST,

(START_DATE + IterNo() - 1) as DAY_ID

WHILE (START_DATE + IterNo()) <= END_DATE;

SELECT SQL......;

This should replace all your code. All you would then need is a STORE to store a QVD.

Hope this helps.

Help with IntervalMatch / Effective Dated records

stephencredmond — Wed, 08 Feb 2012 08:54:42 GMT

Hi Jay,

I have been playing with something similar. My goal is to get the Product cost into my transactions table so, rather than try and expand it out, I am going to join it into the Transactions table with the Interval match, then I can drop the Product Cost table:

ProductPrice:

Load *

From temp_ProductPrice.qvd (qvd);

Outer Join (Orders)

IntervalMatch(DateID, ProductID)

Load

StartDate,

EndDate,

ProductID

Resident ProductPrice;

Left Join (Orders)

Load

StartDate,

EndDate,

ProductID,

Price

Resident ProductPrice;

Drop Table ProductPrice;

Drop Fields StartDate, EndDate;

See how that works for you.

Regards,

Stephen