Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
OK, here we go.
I have recreated the job as a rudimentary structure. In a first step, you create a tDBInput and put in the "max" statement. The schema of this component must just be "count" of type "Integer" (feel free to chose a different column name). If your table contains a lot of records, executing this query may take a while - use the usual query optimisation techniques like indexes in this case (outside this job).
Then you store this max value in a global variable using tSetGlobalVar (increased by one). Name it "count" or whatever.
Then you can use this global variable in your tMap (tMap_1 in the picturre above) and all the records will get the same value for "batch" (which is what you want)
Let us know, if this solves your problem.
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?
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.
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
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?
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?
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.