topic Re: How to add Insert date or Update date in Talend Studio

How to add Insert date or Update date

Keihilin — Fri, 15 Nov 2024 22:11:21 GMT

Hello,

I'm using Talend Open Studio V8.0.1, to map data from excel file to an Oracle SQL database.

the table action is : "Update or Insert". as I expect more update than new item.

But, I would like to fill out 2 field in the database:

LOADDATE
UPDATEDATE

The first one dedicated to the initial load date

the second, updated after each "update" of the item.

I do not see how to proceed to perform this task, could you please help me? is it on the Database item or on the Tmap item ?

Best Regards

Keihilin

Re: How to add Insert date or Update date

Anonymous — Thu, 19 Jan 2023 11:16:42 GMT

The easiest way I can think to do this is to simply have 2 DB components; one for an update and one for an insert. Set up your tMap to use a lookup from the DB table you are writing to and use that to identify if this is a lookup or an insert. Create two outputs from your tMap (one to the insert and the other to the update), then apply the date logic logic there.

This will not reduce the performance noticeably (it may actually improve it slightly) and it gives you far more control when wanting to achieve slightly different outcomes based on whether it is an insert or update.

Re: How to add Insert date or Update date

Keihilin — Thu, 19 Jan 2023 12:22:55 GMT

Thank you for the quick feedback, I will try.

Re: How to add Insert date or Update date

Keihilin — Fri, 20 Jan 2023 10:54:17 GMT

Hello,

So, I added a component of the database (input version) to do get the list of existing items.

Now I'm in the Tmap component, with the new row block on the left, I would like to create 2 variable in the middle column:

if row1.itemName in row4.itemName -> variable UPDATE = true
if row1.itemName not in row4.itemName -> varialbe LOAD = true

row1.itemName => the item to be added in the table

row4.itemName => the lookup to see what is already there in the table

then connect the variable to the right column:

Var.LOAD ==true?TalendDate.getDate("dd-MM-yyyy") > LOADDATE (field)

Var.UPDATE ==true?TalendDate.getDate("dd-MM-yyyy") > LASTPDATEDATE (field)

If i'm not totally offset with the Talend logic, could you please let me know how to do that with Talend language?

Best Regards,

Keihilin

Re: How to add Insert date or Update date

Anonymous — Fri, 20 Jan 2023 11:59:37 GMT

I think you can do this in a much simpler way if I understand what you are doing. You simply need to join your lookup to your main row via itemName and set the "Match Model" to "All matches" and the "Join Model" to "Inner Join". Like in the screenshot below.....

Then create two outputs. The one to carry out the update and the one to carry out the insert. This is managed by the "Catch lookup inner join reject" setting. For the matched data (the data to update) you set this to false (the default). For the unmatched data (the data to insert) you set this to true. Shown below.

This should sort it for you.

Re: How to add Insert date or Update date

Keihilin — Fri, 20 Jan 2023 13:41:19 GMT

Hello,

Thank you for the detailed explanation with screenshot, that's help a lot.

But, something still not clear to me:

At the end, I would like the "current date" in the field UPDATE or LOAD.

so, I see on your last screenshot, that you linked the value row3.newColumn (if I'm right that's the item I'm comparing)

can I replace the value "row3.newColumn" by TalendDate.getCurrentDate() ?

Or, I must link the Row3.newColumn item to the output ?

Re: How to add Insert date or Update date

Anonymous — Fri, 20 Jan 2023 13:57:39 GMT

The "newColumn" column I added was just an example of any column. The columns I used were not important, it was the settings that I wanted to get across. You can use whatever you like in the input and output tables.

TalendDate.getCurrentDate() will work. But you should probably call it in a tMap variable and share it to the LastUpdateDate and LoadDate columns. This is because you may get different times when you share it to the LOAD output.

By the way, your screenshot looks the wrong way around. Your LOAD should have both the LastUpdateDate AND the LoadDate, your UPDATE should only update the LastUpdateDate I suspect.

Re: How to add Insert date or Update date

Keihilin — Tue, 24 Jan 2023 13:57:32 GMT

Hello Rhall,

Thank you for your help, I succeed to perform the task ;o)