Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
It would be very helpful if, somebody could attacch a sample application which uses match function in it.
Though i went through the referrencee manual, but unfortunately didn't clearly understand the explanation about Match () and exists()
~B
This is an example of exists that is a part of Rob Wunderlich's cookbook available at his website robwunderlich.com.
The match function is a way to avoid writing
Load ...
From ...
Where Customer = 'A' or Customer ='B' or Customer='C'
and write it as
Load ...
From ...
Where match(Customer, 'A','B','C')
and it comes in a variety of flavors. WildMatch() is usually my favorite.
Regards, Karl
This is an example of exists that is a part of Rob Wunderlich's cookbook available at his website robwunderlich.com.
The match function is a way to avoid writing
Load ...
From ...
Where Customer = 'A' or Customer ='B' or Customer='C'
and write it as
Load ...
From ...
Where match(Customer, 'A','B','C')
and it comes in a variety of flavors. WildMatch() is usually my favorite.
Regards, Karl
Hi,
In addition to Karl's post, Exists() referes to a previously loaded field to which compares the value that is loading, as opposed to Match() that compares to any given string or expression:
Customers:
LOAD CustomerID,
CustomerName
FROM Customers.qvd (qvd);
Orders:
LOAD OrderID,
OrderDate,
Qty,
Amt,
CustomerID
FROM Orders.qvd (qvd)
WHERE EXISTS(CustomerID);
The Orders table will only load from the file Orders.qvd those where the value in CustomerID already exists in the table Customers, so for example, if Customer C has two orders but it's not in the Customers table, these two orders are not loaded.
Hope that helps.
Miguel
Thank you Karl and Miguel....for being crystal clear...
~B
Quick question.
How about the efficiency of an EXISTS vs. a MATCH? For example if you add some matching criteria to an Inline Table at the beginning of the script and then use EXISTS on a Load. Is that faster than simply doing a MATCH on the load with the same criteria?
Hi Nate,
In my experience, I only use Match() when the number of possible values is very limited, say no more than 10. And besides that, if the value is already in a field that has been loaded previously, it takes more time to build the function starting from the values loaded than simply using the Exists().
Anyway, feel free to try and share you experiences with large data sets and performance.
Hope that makes sense.
Miguel
Hi Miguel.
My problem at the moment with either "OR" or "Match" function is that the moment I specify more than one value to return data for, it seems to "ignore" my criteria and return all values regardless.
Code snippet:
where (budget_code = '2013' or budget_code = '2014');
In this case, it returns every budget_code in existence. If I say where budget_code = '2013' then I get 2013 values only.
The similar thing is happening with match:
where match(budget_code, '2013', '2014') ignores my condition except for when only one value is specified, i.e.
where match(budget_code, '2013').
Do you have any suggestions please?
Thank you.