One Star

One Lookup table data read multiple times tp apply different joins

Hi,
I have one lookup table data in my job and the lookup data volumn is very huge. hence the requirment is executes the one lookup table data once and in tmap source table join conditions is different times.
the reason is same lookup table data compare to different input condtions based on getting diiferenct column value.
Is talend possible lookup data value read multiple time in tmap transformation?
Thanks,
Regards,
Srinivasan S
12 REPLIES
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hi Srinivasan
You can choose "Lookup Model"-->Reload as each row(Cache).
For more info, read --> Chapter 6.9 "Handling lookups".
Regards,
Pedro
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hi Shong,
Thanks for the response. while viewing the PDF documents, its says that handling lookup concepts level only and screenshot for 'load once' type method only.

Can you please show us screenshot, how to assign source table differenct input condition assign to tmap (Lookup Model"-->Reload as each row(Cache)) method.

Thanks,
Regards,
Srinivasan S
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hi Srinivasan
It's the same with screenshot for "load once".
The difference is that "Reload as each row(Cache)" will find data from cache firstly.
Regards,
Pedro
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hi Shong,
Sorry for the inconvience. I think your understanding logic is differ.
My Requirement logic is : (below for the example)
inupt data as :
A TABLE
empno deptno sal comm
100 105 15 25
200 450 25 60
300 605 45 90
The lookup data executes as data values as:
B TABLE
keyvalue result1
100 A1
105 B1
300 S1
200 L1
first need to compare the A and B lookup table condtion as : ( select * from a,b where a.empno =b.keyvalue ) - get the lookup value to tmap target
After that same lookup data job comare with second lookup condtion as ( select * from a,b where a.deptno =b.keyvalue ) - get the result set value to tmap target.
above scenarios is poossilbe in talend?
Please suggest me your ideas.

Thanks,
Srinvasan S
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hi Srinvasan
I have talked with my coworker.
According to your requirement, you have to use two tMap.
The job is like the following image.
Use tReplicate to replicate look-up flow.
One more thing. I'm pedro. Shong is my coworker and my leader. Smiley Wink
Regards,
Pedro
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hai Pedro,
Thanks for the response. we have seen your attached screenshot.
In my case is.
We using only one source table, how to replicate the lookupdata?

Thanks,

Regards,
Srinivasan S
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hi
You have to put source table twice.
Regards,
Pedro

Re: One Lookup table data read multiple times tp apply different joins

Srinivasan,
You can also cache the data once using a tHashOutput, and the reuse it multiple times with a linked tHashInput.
here's what it would look like:
--row-->tHashOutput
|
onOK
|
---row--->tMap---->output
| ----|
|
onOK
|
---row-->tMap---->output
-------|
One Star

Re: One Lookup table data read multiple times tp apply different joins

Hi JohnGarrettMartin and shong,
Thanks for the good suggestion. But am not able to find thashinput and thashoutput component in talend 5.0 version.
where can i find this component in tos 5 version?
Also one more thing i would say that.
In a single job, will able to design the code number of lookup queries store to number of thashoutput component. since we used number of lookup queries to multiple time compare with source table.
Please help me on this.

Thanks,
Srinivasan
One Star

Re: One Lookup table data read multiple times tp apply different joins

I know this is a month or so late but I've had almost exactly the same problem, this is how I solved it, it would be nice if you could use treplicate to do this as you can get some funny behaviour with tHash's with nested jobs and can be quite memeory intensive but when it does work it can be very quick.
tHashInput and tHashOutput are in the technical section of the pallete although you may need to turn them on.
One Star

Re: One Lookup table data read multiple times tp apply different joins

You can alias the lookup table. We had a requirement where columns c1,c2,c3,c4,c5 and c6 of table A have references on column p of Lookup table L i.e. Multiple columns from table A have references to single column in lookup table L.
We achieved by alisaing the lookup table 6 times and connecting all the aliases and the source to tmap and it worked fine without any issues.

regards,
Jeevan Anaparthy
ETL Architect
CSC, Chennai
One Star

Re: One Lookup table data read multiple times tp apply different joins

You can alias the lookup table. We had a requirement where columns c1,c2,c3,c4,c5 and c6 of table A have references on column p of Lookup table L i.e. Multiple columns from table A have references to single column in lookup table L.
We achieved by alisaing the lookup table 6 times and connecting all the aliases and the source to tmap and it worked fine without any issues.

regards,
Jeevan Anaparthy
ETL Architect
CSC, Chennai

Since I have exactly the same requirement, I'd like to know what "aliasing a table" means and how it can be achieved in talend?!
EDIT:
I think I know what was meant but it's not the best solution in my oppinion.
I have the following problem
SourceTable
Error Code 1 | Error Code 2
100 | 200
200 | 200
300 | 100
100 | 200
Now I have a ErrorCode Dimension with all the possible Codes (about 10000). Now, if I use two tMap Components and two tDbInput's (from the same Dimensiontable) each Component hats to load the 10000 Codes, what takes a while. Any Alternatives?