I am having one scenario in my project.
I am getting data from sap table with Fields like
If the ISACTIVE field is null then that record will be the active record and we will receive only the active record from source.
the data will be the real time data and refreshed every 15 mins.
in the next run i can get the same OBJNR record but the active ID might be different.
I need to load in aurora database and the output should be..
For the first run
For the second run it should be
ideally i need to update the old records with inactive status("X") for the same OBJNR and insert the new record with active status(null)
I know i can seperate the flow one for update the old record and another for inserting the new record.
But i want to know is it possible to do both update and insert in th same subjob
if the OBJNR is present in the target then i want to update the status with "X" and i want to insert the new record coming from the source.
Early Response will be helpful..
@Gayathri,you need to compare your source against with target table,which you are loading to aurora,if you find a record then you need to set x as ISACTIVE in tMap.
flow would be like this: do lookup on OBJNR andin the output section for the ISACTIVE filed as like target.OBJNR ==null ?sap table.ISACTIVE :"X",since i am assuming ISACTIVE as string.
sap table-->Main to tMap-->target
Target table-->lookup to same tMap
thanks for the reply..
still i am having a doubt like even-though if i am taking target lookup if the objnr matched it will update the record so that i can get the old record updated as inactive , then how will i get my new record inserted in the same flow..
whether it is possible with the below job logic??
@Gayathri,Based on my solution it will update to x for the matched record even it is already marked to x, while pulling from target you need pull the ISACTIVE is null records and do the lookup then you will not update the updated records.
Thanks for the reply..
i understand the update of old record will done properly, whereas my question is how can i insert the new record without using another subjob. (need to perform the insert of new record with same objnr with different id as active in the same flow)..
@Gayathri,in the same job itself you can identify the records,in tMap insert and update to output sections and do what do you want.
You can do this request in a single job. Attached is the skeleton of the job you are planning to build. You will have to add two additional components to palette by going to File -> Edit Project Properties -> Designer -> Palette Settings (add tHashInput and tHashOutput).
In the tmap, I am catching all the matching records in currently in DB to another flow as below.
I have attached the zip file of the job (Talend Version 7) as attachment for reference. It is a skeleton job and you need to give connection details to make it run. Sometimes, older version of Talend may not be able to process the zip file from Talend 7. In that case, please refer the diagram to complete the flow.
If the reply has helped you, could you please mark the topic as solution provided? Kudos are also welcome :-)
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema