topic Re: Script not executing IntervalMatch in QlikView

Script not executing IntervalMatch

richard_chilvers — Wed, 08 Jan 2014 16:01:52 GMT

I have a script which uses the Extended Syntax form of IntervalMatch. When I execute it, it seems the IntervalMatch is skipped over altogether without any errors. I would have expected the IntervalMatch table would always be created (although if I have done it wrong, it may have no rows)?

This is the statement:

INTERVALMATCH(XSDP_DATE_POSTING, XSDP_XCET_ID) LOAD DISTINCT XPRD_DATE_OPEN, XPRD_DATE_CLOSE, Vessel_Code RESIDENT Cruises ;

As a separate, related question, with IntervalMatch work only on dates, or any numeric values?

Any advice is appreciated.

Re: Script not executing IntervalMatch

swuehl — Wed, 08 Jan 2014 16:07:19 GMT

I think the Table you apply the LOAD prefix to is missing the extended IntervalMatch key, isn't it?

Maybe you can describe your tables and requirements a bit closer, this surely helps in resolving your issue.

To your second question, it should work with all numeric intervals.

Re: Script not executing IntervalMatch

richard_chilvers — Wed, 08 Jan 2014 16:19:24 GMT

I'm not sure I understand. I am hoping to match the key called XSDP_XCET_ID to Vessel_Code. Have I misunderstood the format ?

Regards

Re: Script not executing IntervalMatch

Wed, 08 Jan 2014 16:22:39 GMT

From QV Help Doc....
"The syntax is:

intervalmatch (matchfield,keyfield1 [ , keyfield2, ... keyfield5 ] ) (loadstatement | selectstatement ) "

Re: Script not executing IntervalMatch

swuehl — Wed, 08 Jan 2014 16:23:11 GMT

Well, it's probably me who's not understanding.

I believe you need to state a field as key that you are actually using in your LOAD, like in

INTERVALMATCH(XSDP_DATE_POSTING, Vessel_Code) LOAD DISTINCT XPRD_DATE_OPEN, XPRD_DATE_CLOSE, Vessel_Code RESIDENT Cruises ;

What's that second field XSDP_XCET_ID for or coming from?

Re: Script not executing IntervalMatch

jpapador — Wed, 08 Jan 2014 16:24:23 GMT

The key fields have to be named the same I believe. Vessel_Code should be aliased to XSDP_XCET_ID

Re: Script not executing IntervalMatch

Wed, 08 Jan 2014 16:25:19 GMT

Try using INNER JOIN.

Example:

Load Key, Name, Date, Sales

from Transact.xls;

INNER JOIN

intervalmatch (Date,Key)

Load FirstDate, LastDate, Key

resident Key;

Re: Script not executing IntervalMatch

richard_chilvers — Wed, 08 Jan 2014 16:45:25 GMT

Thanks to jpapador and swuehl who both agree that the key field(s) need to be identical.

I think IntervalMatch is tricky and not well documented so I will give this a try !

Re: Script not executing IntervalMatch

hic — Wed, 08 Jan 2014 16:59:45 GMT

Check these documents out: They may be helpful

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions

http://community.qlik.com/docs/DOC-4310

HIC