Five Stars

REG : Performing Update and Insert operation in same flow

Hi Team,

I am having one scenario in my project.

I am getting data from sap table with Fields like

OBJNR;ID;ISACTIVE
100;E001;null

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.

EX:
OBJNR;ID;ISACTIVE
100;E002;null

I need to load in aurora database and the output should be..

For the first run

OBJNR;ID;ISACTIVE
100;E001;null

 

For the second run it should be

OBJNR;ID;ISACTIVE
100;E001;X
100;E002;null

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..

 

Thanks-

Gayathri S

 

6 REPLIES
Twelve Stars

Re: REG : Performing Update and Insert operation in same flow

@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

 

Manohar B
Five Stars

Re: REG : Performing Update and Insert operation in same flow

Hi Manohar,

 

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??

 

Thanks-

Gayathri S

Twelve Stars

Re: REG : Performing Update and Insert operation in same flow

@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.

Manohar B
Five Stars

Re: REG : Performing Update and Insert operation in same flow

Hi Manogar,

 

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)..

 

Thanks-

Gayathri S

Twelve Stars

Re: REG : Performing Update and Insert operation in 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.

Manohar B
Employee

Re: REG : Performing Update and Insert operation in same flow

Hi Gayathri,

 

       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).

image.png

 

 

In the tmap, I am catching all the matching records in currently in DB to another flow as below.

image.png

 

 

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 :-)

 

Warm Regards,

 

Nikhil Thampi