Help with IntervalMatch

Thu, 19 Jan 2012 22:47:49 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

Re: Help with IntervalMatch......Or better way??

Thu, 26 Jan 2012 14:26:55 GMT

Any suggestions from the QV masters?? Really like to get something like this working, or understand new method.

Thanks, Jay

topic Help with IntervalMatch in QlikView

Help with IntervalMatch

Re: Help with IntervalMatch......Or better way??