Count "*" as passing match in tMap lookup

One Star

Count "*" as passing match in tMap lookup

I currently have a lookup file that includes the '*' character intended as an unconditional 'pass'. How can I have tMap count the * in the lookup field as a match (in addition to regular matches)?
Example of desired match:
Incoming field: "same text"
Lookup field matches: "same text" || "*" // also matches "*"
Example of NOT matching:
Incoming field: "same text"
Lookup field: "other text"
Thanks,
R
One Star

Re: Count "*" as passing match in tMap lookup

Hi rwhelan,
This is what I understood from your explanation :
1) You have one main file.
eg: table1
text id
----------
hello 1
sample 2
2) You have a lookup file.
eg: table2
text value
------------------
sample matched1
* matched2
hello matched3
And you need to join these two tables based on the below conditions:
table1.text = table 2.text or table2.text = *
Thanks,
Dwanny
One Star

Re: Count "*" as passing match in tMap lookup

Yes, Dwanny, thank you.
You are correct. I am using a tMap, should I be using tJoin? (I have other lookups as well so I thought I could include it all in one tMap.)
I encountered a problem referring to table2.text when setting the table2 expression key. For table2 Expr. Key I entered:
table2.text.equals("*") ? table2.text : table1.text;
But I get the error:
table2 cannot be resolved
table2 cannot be resolved to a variable
How can I reference table2 in its own expression key statement?
Thanks again,
One Star

Re: Count "*" as passing match in tMap lookup

Hi rwhelan,
Please find attached the screenshot of a job for your requirement.
main table
-------------
text id
hello 1
sample 2
Lookup
---------
text value
sample matched1
* matched2
hello matched3
Output
---------
text value text_1 value_1
hello 1 hello matched3
sample 2 sample matched1
hello 1 * matched2
sample 2 * matched2

Regarding the error,
table2 cannot be resolved
table2 cannot be resolved to a variable
If table2 is your table name, try replacing table2 with the connection name, i.e like row1.text instead of table2.text.
Thanks,
Dwanny
One Star

Re: Count "*" as passing match in tMap lookup

Dwanny, thank you again for your response.
For each input row, I need to match the one best row from the lookup input, so that I can take a single value from its last column for an important calculation.
The lookup input has several columns (5, but this is not relevant). The first four are used for matching. A row is accepted as a match if its field values are either an exact string match with the respective main input, or if the lookup field value is "*".
It is possible (and likely) that multiple lookup rows will be matched for a single main row. In this case, the best matching lookup row will be used for the final calculation using that fifth value. 'Best' is determined as the lookup row with the most matching full strings (ie the least number of '*'). Fortunately the lookup file is ordered so that rows with more full strings are before rows with "*", but each row may contain a mixture of full strings and "*" in its four lookup fields, so hopefully the first match should always be the right one.
The problem I'm having is how to select lookup rows that either have a full string or a "*". It's easy to only match the full strings directly. It's also easy to just match "*". How can I get both?

R
One Star

Re: Count "*" as passing match in tMap lookup

Hi rwhelan,
My understanding is as follows:
1) There is a main flow.
2) There are many lookup tables having 5 columns.
3) For a given row in the main flow, we compare values in 4 of its columns to each row of the lookup tables.
4) A Row in a lookup table matches with the main flow, when
a) Values in 4 columns of main flow exactly matches with a row in lookup table.
b) For values in the main flow, the corresponding column in the lookup row have "*" values.
5) After identifying the matching rows in the lookups, we select that particular row having maximum match with the main flow. This row will be identified with the below priority:
a) The row having all columns matching with input row.
b) The row having minimum number of columns having "*" value matching with the main flow.
6) You select the value in the 5th column of the identified row from the lookup and proceed with your calculations.
Thanks,
Dwanny
One Star

Re: Count "*" as passing match in tMap lookup

Yes, you pretty much said it.
One Star

Re: Count "*" as passing match in tMap lookup

Hi rwhelan,
I was working on your requirement. Please find the attached screenshots. This job is designed for 3 lookups ( you can ofcourse make it 4 as per your requirement).
Please revert if you have any queries.
Thanks,
Dwanny
One Star

Re: Count "*" as passing match in tMap lookup

Thanks Dwanny, I solved it using tSQLiteInput and did have to use a query.