
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Could somebody explain match() function and exists()?
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
Accepted Solutions

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
.png)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Karl and Miguel....for being crystal clear...
~B


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
.png)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
