Look up to get max id

[A_San]

New to talend. I am trying to load a table say stg0 from a source file. one of the columns in target is batch_id. For every run, the batch id should increment by 1. 

For ex: If I load file 1 and later file 2, the batch id should be 1 for file 1 and 2 for file 2. So I should look up on the STG0 table and get the max batch id and increment by 1. I am trying to use tmap to achieve this. My main connection to tmap is the source delimited file and look up table is STG 0. In tmap, I cannot join between source file and stg0. I am not sure how to go about this. Any help would be appreciated.

[root]

One way I can think (if I understand your question well ), is:
Read the stg0 table and find the max batch_id and save it in a context/ globalMap variable.

Then, load the file and connect it to target via tMap.

In the tMap, where the target batchid is present, set the value as the context/ globalMap variable + 1

HTH.

[A_San]

Do you mean to say not to have a look up?

I can try that. Could you please let me know how I can save it in a context/ globalMap variable ? Should this be done in Tmap?

[A_San]

This is what I have currently. Please see screenshot of my job

SS1.PNG

[root]

I would have tDBInput --> tJavaRow where I capture the output of the query. Then, assign that value to context/ globalMap:

lets say you have the query as: select max(id_batch_id) as 'current_max_batch_id' from stg_0_id_cntl_nmc;
Keep this id as Int in the schema

// in tJavaRow
// setting context
context.current_max_batch_id = input_row.curr_batch_id
OR
// setting via GlobalMap
globalMap.put("curr_batch_id" = input_row.curr_batch_id;

Then, on subjob ok, you can do the file list --> fileinputdelimited --> tMap --> tDB Output

in tMap, for the target field of batch_id, you can use the expression
// if context:
context.current_max_batch_id +1
or
// (int)globalMap.get("current_max_batch_id") + 1;
// (Integer)

HTH.

[JR1]

Or without custom coding using tJavaRow, you could have your tDBInput component's output (column name "count") end in a tSetGlobalVar and set the value to row1.count (replace row1 with the name of your connector). You could then use this variable in your tMap (without any lookup) and raise it by one using

((Integer)globalMap.get("count")) + 1

as an output expression. If you can solve something without custom code components, you should do so.

[A_San]

I am getting this error in tJavaRow.

Error in the component's properties:current_max_batch_id cannot be resolved or is not a field

Also how do I link tJavaRow to the main flow which is file list --> fileinputdelimited --> tMap --> tDB Output

[A_San]

I am not sure I follow this - tDBInput component's output (column name "count") end in a tSetGlobalVar and set the value to row1.count (replace row1 with the name of your connector). Could you please explain? I am very new to Talend.

tSetGlobalVar is also a custom code, isnt it?

[JR1]

OK. Looking at your job, I have an additional question. Do you want the number to increase withing the job, i.e. for each file you are loading in this one job, or do you want this to increase between job runs as well, i.e. yesterday this job ran and in the end the number was 164 and today it should start with 165?

[A_San]

Batch Id should increase between job runs. I have another column file_id.

When I run the job today, the batch id will be say 1 and if there are 2 files to be loaded in this batch, then the file id will be 1 and 2 for the respective files.

Next time when I run the job, the batch id will be 2 and the file id will be reset to 1 and depending on the no. of files, it will be incremented by 1.

Please see attached screenshot for example.

SS2.PNG