topic Re: How to pass values to lookup table in Talend Studio

How to pass values to lookup table

A_San — Sat, 16 Nov 2024 04:49:17 GMT

I have a set of files to load say 3 files. I first load the STG0 load control table in which I generate batch id and file id and derive the file name (((String)globalMap.get("tFileList_1_CURRENT_FILE")) ). So When I load 3 files in one batch and 2 files in the next batch, the target will look like

BATCh ID	FILE ID	FILE NAME
1	1	FILE1
1	2	FILE2
1	3	FILE3
2	1	FILE4
2	2	FILE5

Next I have to load the STG1 table with the data coming from the file along with the batch id and file id generated in the STG0 table. I created a tDBInput pointing to STG0 table as the look up as shown in attached screenshot. But I am not sure how to create a variable to store file name and pass it on to the look up to get batch id and file id. I am new to talend. Any help would be appreciated.

Re: How to pass values to lookup table

fdenis — Fri, 30 Aug 2019 08:50:03 GMT

you can use global variables of step 1 into your step 2.
but I did not see where did you set file Id.
on my point of view Batch id have to be set as context and it must be defined by the caller job.
file ID is ((Integer)globalMap.get("tFileList_1_NB_FILE"))
good luck

Re: How to pass values to lookup table

A_San — Fri, 30 Aug 2019 16:17:31 GMT

Batch id and file id are generated in another job say Job1 that loads the STG0 table. Now I am getting Job2 ready to load STG1 table.

I have attached the screenshot of my job1 for reference.

Could you please let me know where exactly I should declare the global variable?

SS12.PNG
SS11.PNG

Re: How to pass values to lookup table

fdenis — Mon, 02 Sep 2019 09:06:01 GMT

on each iteration after file list you can get global variables globalMap.get("tFileList_1_xxx").

if you generate file name into a ^ql table you may use tFlowToIterate who help you to define variables.

Re: How to pass values to lookup table

A_San — Tue, 03 Sep 2019 18:44:28 GMT

I already have the file name in the STG0 table in job1. I just need the batch id and file id from the STG0 table for the respective files and load in STG1 table in job2.

When you mentioned this "on each iteration after file list you can get global variables globalMap.get("tFileList_1_xxx").", do you mean to have tSetGlobalVar after tFileList?

Re: How to pass values to lookup table

fdenis — Wed, 04 Sep 2019 08:51:08 GMT

global variables are define into tFileList you can use them into all component who are behint tFileListe.
You do not have to re-ret global variables.
if you have file name into your db you did not need filelist you just have to get row from a db, use tflow to iterate to defin global varriables then use global variable to read file.
you can define batch and file ids ito your db.

Re: How to pass values to lookup table

A_San — Wed, 04 Sep 2019 21:43:13 GMT

I am not sure I understand. I am new to Talend. I want to use the file names in the job2 to look up on the table STG0 to get batch id and file id.

If there is a look up query then it would be like

Select batch_id,file_id from STG0 where STG0.filename = <filename from job2>

I have attached a screenshot of the job2. I am getting the following error.

Error in the component's properties:The left-hand side of an assignment must be a variable
Syntax error on token "tFileList_1_CURRENT_FILE", invalid AssignmentOperator

SS13.PNG

Re: How to pass values to lookup table

JR1 — Thu, 05 Sep 2019 15:57:41 GMT

Can you please try the following in your lookup query:

"select Id_batch, Id_fil_id, name from stg_0_Id_cntl_nmc where Id_fil_name = '" + ((String)globalMap.get("tFileList_1_CURRENT_FILE")) + "'"

Re: How to pass values to lookup table

A_San — Thu, 05 Sep 2019 16:26:48 GMT

Thank you JR. The query worked without errors but it did not return any rows. Please see my attached screenshots (job, stg0 table and tmap mapping).

Could it be because the tDBInput is not connected to the tFIleInputDelimited directly?

SS14.PNG
tmap.PNG

Re: How to pass values to lookup table

JR1 — Thu, 05 Sep 2019 16:52:43 GMT

Try the following for your lookup query

"select Id_batch, Id_fil_id, Id_fil_name from stg_0_Id_cntl_nmc where Id_fil_name = '" + ((String)globalMap.get("tFileList_1_CURRENT_FILE")) + "'"

Then add the column Id_fil_name to your tDBInput schema. In the tMap put ((String)globalMap.get("tFileList_1_CURRENT_FILE")) as the "Expr. key" of column "Id_fil_name" in row2.

Let us know how this works out.

Re: How to pass values to lookup table

A_San — Thu, 05 Sep 2019 18:41:25 GMT

Still no luck. 0 records are returned from the look up. Attached screenshots.

tmap.PNG
SS15.PNG

Re: How to pass values to lookup table

A_San — Thu, 05 Sep 2019 19:20:21 GMT

JR,

I tried giving the query as "select LD_FIL_NAME,LD_BATCH_ID, LD_FIL_ID from stg_0_ld_cntl_nmc ". I removed the where condition.

I retained the LD_FIL_NAME in the tmap as you had suggested. Now I see 3 records were returned from the look up and the STG1 table got populated properly.

Thank you so much for your help.