How to pass values to lookup table

[A_San]

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.

[fdenis]

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

[A_San]

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

[fdenis]

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.

[A_San]

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?

[fdenis]

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.

[A_San]

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

[JR1]

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")) + "'"

[A_San]

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

[JR1 · Accepted solution]

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.