Write datas into xlsx file after lookup into DB

[Fernandez]

Hi,

In input I have a xlsx file with one column "NAME" containing a list of names.
In output I have to create another wlsx file with 2 columns : NAME / CITY.
For each name in input, I have to check in a DB if it exists :
- if yes, I get the associated city from DB
- if no, I have to put "NOT FOUND" in the xlsx file

Below my job which is ok for the name found in DB:

But for the name not found in DB, how to add the couples NAME and the constant "NOT FOUND" in the xlsx file ?

Thanks

[Anonymous]

A better way of doing this is to the tMap component. Link your "ReadFile" to the tMap first (your main input) and link your "SelectCity" to the tMap second as a lookup. Then you can set the "SelectCity" query to fire for every "NAME" column retrieved from your "ReadFile" by following what I am doing in step 1 of this Community post....

https://community.talend.com/t5/Design-and-Development/Row-Multiplication/m-p/39776

You will want to use a left outer join on the tMap. Then you can apply your conditional logic based on whether a value is returned from your lookup.

[Fernandez]

Thanks for feedback.

But I had simplified my job for explaining my request and actually it's a little bit more difficult beacause depending on the format of the name, I have to look for the city either with strict equality either with operator "like...".

So i think i can't use your solution....

[Anonymous]

I don't understand why you wouldn't be able to use what I suggested. Nothing you have told me precludes this method. Can you explain why you don't think it will work?

[Fernandez]

If i well understood your suggest, I'll get a job like this :

But I don't know how to implement in the tMap the rule below:

If the name contains the character "-", I have to look for in the DB with strict egality (Select City From Table Where Name=xxxxxx)

If the name doesn't contain the character "-", I have to look for in the DB with operator like (Select City From Table Where Name like 'xxxxxx%')

[Anonymous · Accepted solution]

There are a couple of ways you can implement this. Remember you are just modifying a SQL query which is written as a Java String. So, let's assume your query is like this....

"Select
Column1,
Column2,
Column3
From Table"

You need to add a WHERE clause which depends on the value of the data that you are setting in the globalMap of the tMap. You can either add your code inline in the query above. But this might lead to some complicated inline if conditions. An easier way would be to create a Java method to do it for you. For this you can create a routine.  Go to your routines (left sidebar) and create a new routine (in the example below my routine is named "TestUtils"). This is essentially just a Java class. You need to add a static method to carry out your logic. So, a quick demonstration given the logic you have given me would be as below....

package routines;
public class TestUtils {

        public static String setQWhereClause(String value) {
        	String returnVal = "";

        	if(value.indexOf("-")>-1) {
        		returnVal = "WHERE Name = '"+value+"'";
        	}else {
        		returnVal = "WHERE Name LIKE '"+value+"%'";
        	}
        	
               return returnVal;
        }
}

Now you would simply add this to your SQL query above like this....

"Select
Column1,
Column2,
Column3
From Table " + routines.TestUtils.setQWhereClause(((String)globalMap.get("your_keyname")))

The globalMap is getting the value you should have configured in your tMap using the "Reload on each row" feature.

The query will adjust for every row.

It's that simple 🙂

[Fernandez]

I implemented your solution and I've progessed !

But I don't know why, I only get in the result file the lines not found in DB.

And I don't have the lines found in DB (in screenshot below we can see nevertheless 44 rows for output named "Found".)

Below the configuration of tMap:

[Fernandez]

It works if I write output datas in two distinct xlsx files.

I don't understand why I can't write in the same output file.

Below my configuration of tFileOuputExcel :