multiple columns in source to look up on cache single column

[Sudhee_Maximus]

Hi All,

I have a peculiar requirement to join multiple columns (7 columns of language value lang1,lang2,lang3 ....lang 7 coming from file) with one cache table Language (has 2 columns value ,description column). join to be made is  lang1 =value,lang2=value lang3=value ...to lang4=value.

This is easily achievable in sql world , just checking if any option in talend where we can acheive this without repeating cache 7 times with tmap  to join 7 different columns.

[Anonymous]

Where is the data stored? If it is all in the same DB, you can use SQL to do this when you bring the data into Talend. Alternatively you will have to join 7 times using a tMap.

[Sudhee_Maximus]

Hi Rhall,

Thanks for your reply ..

The source is from a file(fixed positional) and the cache (lookup table) is created using api as source (caching in thashbuffer).

I don't have DB else would have preferred doing joins at DB level instead of Talend.

I want to avoid using multiple joins in tmap , hence had this post created :-).

[Anonymous]

There is another way then. The data from your file (lang1, lang2, lang3, etc), is there any reason why it needs to remain in 7 columns? Could you not convert it to a single column with an identifier column added? So, for example....

Lang1, Lang2, Lang3, Lang4, Lang5,.....
"Hello", "Guten Tag", "Bonjour", "Hola", "Salve"

...to ....

Lang, Key

"Hello", "Lang1"
"Guten Tag", "Lang2"

"Bonjour", "Lang3"

"Hola", "Lang4"

"Salve", "Lang5"

Doing this would only require 1 lookup of your cache

[Sudhee_Maximus]

rhall_2_0 , 

good Idea and thanks! we are thinking alike ... 🙂 , thought of it but  (it is 92 column file )and  there is one more column in same file which has 10 different columns like (p_specl_cd1 ... to cd10) this 2 columns if we split in to rows going to be huge no of records and duplicates (checking on any other option...). there are 2 different cache's I need to join for these 2 columns .

not sure if this can be solved like this ... Thanks a lot for your time on this , let me know if you have any other idea apart from the ones discussed now :-).

[Anonymous]

How big is your cache dataset? You could (and this is more complicated) add this data to a Java HashMap and set the first column (Lang) to be the key and the description column as your value. You would then be able to carry out the lookup by simply referencing the HashMap with a .get() call. This would require a step where you load the data into the HashMap, but that shouldn't be too difficult if you are OK with Java.

[Sudhee_Maximus]

Hmm, this sounds interesting will try this out . I am not good at java but will try to figure this.

appreciate your response and Thanks a lot ! 

[Anonymous]

I've knocked up a quick example. Here is the job.

I have used a tRowGenerator to get some test data which is a name and a random string. The tJavaFlex code is where the data is loaded into a HashMap. This shown below....

Start Code

// start part of your Java code
java.util.HashMap<String, String> hm = new java.util.HashMap<String, String>();

This code initialises the HashMap.

Main Code

// here is the main part of the component,
// a piece of code executed in the row
// loop
hm.put(row1.name, row1.value);

This code assigns the value field to the name as a key.

End Code

// end of the component, outside/closing the loop
globalMap.put("hm",hm);

This code assigns the hm HashMap to the globalMap.

It is then used in the tMap like so....

The code in the output value column is below....

((java.util.HashMap<String,String>)globalMap.get("hm")).get(row2.name)  

The above code retrieves the hm HashMap from the globalMap and then the value for the row2.name column is retrieved from the hm HashMap.