Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Help with IntervalMatch

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

1 Reply
Not applicable

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

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

Thanks, Jay