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
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
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,
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
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?
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
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