topic Re: Which execute fast Join or Interval Match in QlikView

Which execute fast Join or Interval Match

Karim_Khan — Thu, 29 Oct 2015 15:51:41 GMT

Hi Team,

I was executing one script and it got executed successfully.Now My concern is that which function executes fast

Join or Interval Match().Please help for the same.

Why Synthetic key is getting created when it doesn't have more than one common field name?

Regards,

MRKachhiaIMP‌jagan‌qlikviewwizard‌gargi.bardhan‌swuehl‌

Re: Which execute fast Join or Interval Match

MK_QSL — Thu, 29 Oct 2015 15:55:14 GMT

I would go with IntervalMatch if you are matching a field of one table to interval of two fields.

IntervalMatch will create a Synthetic Key but you can join the table after intervalmatch and drop 2nd table.

Re: Which execute fast Join or Interval Match

Karim_Khan — Thu, 29 Oct 2015 16:03:31 GMT

But how could you pls elaborate i am little bit confused.

Re: Which execute fast Join or Interval Match

MK_QSL — Thu, 29 Oct 2015 19:08:49 GMT

Data:

Load * Inline

[

Item, FromDate, ToDate, Price

I001, 01/10/2015, 15/10/2015, 100

I001, 16/10/2015, 31/10/2015, 120

I002, 01/10/2015, 10/10/2015, 90

I002, 11/10/2015, 25/10/2015, 110

I002, 26/10/2015, 31/10/2015, 100

I003, 01/10/2015, 25/10/2015, 125

I003, 26/10/2015, 31/10/2015, 120

];

Sales:

Load * Inline

[

Item, QtySold, InvoiceDate

I001, 10, 12/10/2015

I001, 8, 18/10/2015

I002, 20, 16/10/2015

I002, 25, 30/10/2015

I003, 5, 15/10/2015

];

Inner Join

IntervalMatch(InvoiceDate, Item)

Load FromDate, ToDate, Item Resident Data;

Left Join (Sales) Load * Resident Data;

Drop Table Data;

Re: Which execute fast Join or Interval Match

MK_QSL — Thu, 29 Oct 2015 19:10:51 GMT

Check enclosed file on your sample qvw..

Re: Which execute fast Join or Interval Match

MK_QSL — Thu, 29 Oct 2015 19:15:27 GMT

My same example with If rather than IntervalMatch. This is for example only. I would not prefer this method..

Data:

Load * Inline

[

Item, FromDate, ToDate, Price

I001, 01/10/2015, 15/10/2015, 100

I001, 16/10/2015, 31/10/2015, 120

I002, 01/10/2015, 10/10/2015, 90

I002, 11/10/2015, 25/10/2015, 110

I002, 26/10/2015, 31/10/2015, 100

I003, 01/10/2015, 25/10/2015, 125

I003, 26/10/2015, 31/10/2015, 120

];

Sales:

Load * Inline

[

Item, QtySold, InvoiceDate

I001, 10, 12/10/2015

I001, 8, 18/10/2015

I002, 20, 16/10/2015

I002, 25, 30/10/2015

I003, 5, 15/10/2015

];

Left Join (Sales) Load * Resident Data;

Drop Table Data;

SalesFinal:

Load

Item,

QtySold,

InvoiceDate,

Price

Resident Sales

Where (InvoiceDate >= FromDate and InvoiceDate <= ToDate);

Drop Table Sales;