One Star

How to reload a lookup table in a tmap after each iteration

Hi,
I'm trying to build an histtory table (tbHIST) thanks to a big table (TDR20) that contains full data for each days and a date dimension table.
The aim is too loop into the dim date table to retrieve a date that will filter the miror table (TDR20) and map it with the
target table to evaluate the input data and define whether it is a new row to add or an ins/upd.
The issue is that when a use the 'run the lookup table once' (look up table that in my case is also the target table (LKP_PREP_ENDBALANCE))
in the tmap component it's not refreshing my lookup table on the second day with the date inserted in the previous date and it's loading full day each time.
I tried to use the the (run each rows) but it's almost loading 20 rows by second because for each rows of the mirror table TOS is refreshing the lookup table fullyl which in my case it's not usefull as I need a refresh by date (iteration).
So my question is how to reload the tmap lookup table after each iteration how how to exclude the iteration from the main job
(I'm using TOS 3.1)
here is the line in the TDR20 input where I'm applying the date received by the dim_dates
AND TDR_DATE ='" + globalMap.get("row1.DATE_ORA") + "'
10 REPLIES
Community Manager

Re: How to reload a lookup table in a tmap after each iteration

Hello
Have you select 'reload at each row' option?
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to reload a lookup table in a tmap after each iteration

Yes that's what I was saying in my first post.
I tried to use the the (run each rows) but it's almost loading 20 rows by second because for each rows of the mirror table TOS is refreshing the lookup table fullyl which in my case it's not usefull as I need a refresh by date (iteration).

the issue is that it reloads the lookup table for each row not for each iteration (loop)
One Star

Re: How to reload a lookup table in a tmap after each iteration

Hi drari,
I encountered the same issue. I don't know if it's the best way, but I resolved it by creating 2 jobs.
I have a father job like: tOracleInput ---> tFlowToIterate ----> tRunJob, in which the tRunJob calls a child job. The child job contains all the components after the iteration.
With this structure, I'm sure that the lookup table is reloaded at each iteration. ;-)
Hope this helps,
Best regards,
Karine
One Star

Re: How to reload a lookup table in a tmap after each iteration

Hi Kboop,

I will be happy to try this, could you just explain me how i can retrieve the value of the dim date (that will be in the main job )
to push it in the second job that contains my tmap and at the same time to filter my tdr20 table with the retrived date?
thank you again
Drari
ps: is the joblet feature only available in TIS
One Star

Re: How to reload a lookup table in a tmap after each iteration

Hello,
You can use this component, which can be use as a replacement for joblet :
http://www.talendforge.org/exchange/tos/extension_view.php?eid=155
I notice I had to correct the classpath, when I export the job as a POJO though.
One Star

Re: How to reload a lookup table in a tmap after each iteration

To transfer the dim date to the child job, you can create a context parameter in the child job and use it in your TDR20 input:
AND TDR_DATE ='" + context.date + "'
Then, in the tRunJob settings, add the corresponding globalMap variable to this context param (see attached screenshot)
Best regards,
Karine
One Star

Re: How to reload a lookup table in a tmap after each iteration

Karine,
thank you for your great help
But now I think I have another issue I'm not able to add a new parameter in the TrunJob component the '+' sign is gray
Drari
One Star

Re: How to reload a lookup table in a tmap after each iteration

I think it's because you have to create the context parameter in the child job first, in the "Contexts" view.
Did you create it ?
Karine
One Star

Re: How to reload a lookup table in a tmap after each iteration

yes you right I created in the child job and it's visible now.
Now it's working perfecty thank you for your great support.
Just a nice to have question
is it possible to see the subjob (called by the tRunJob feature) running when executing the main job?
Drari
One Star

Re: How to reload a lookup table in a tmap after each iteration

You're welcome ;-)
About your last question, only the main job traces are displayed when executing it.
If your aim is to test your child job, may be you can execute it alone or use some tLogRow components to display some flows in the console.
Karine