Which execute fast Join or Interval Match

[Karim_Khan]

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,

KK

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

[MK_QSL · Accepted solution]

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.

[Karim_Khan]

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

[MK_QSL]

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;

[MK_QSL]

Check enclosed file on your sample qvw..

[MK_QSL]

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;