Insert and update a table

Question

Hi guys,

Lets say we have a table :

ID               AGE           STARTDATE                 ENDDATE

S7               29                25032019                           -

S310           37                28032019                           -

and an updated table is coming with these data :

ID              AGE           STARTDATE

S7               30               25032020

S310           37               28032019

The desire table would be like that :

ID            AGE         STARTDATE           ENDDATE

S7             29               25032019             24032020

S7             30               25032020                    -

S310         37               28032019                    -

So this i want,is when the new update file has the same ID and different age,we want to keep history so we fill the enddate with the (startdate-1 of the newfile) and we create another row with the new updated values ,otherwise the data will be the same..

How can i implement this?

Thank you

AbhilashR · Accepted Answer

@Anastasio_Theohari - I dont have a database on my local machine to give you an actual working example reading and writing to db, but I have put together this broad flowchart to give you an idea of how I would attempt to solve it.

In my workflow I am identifying relevant ID rows from existing table and unioning them with the new data, and then use a multi-row formula tool to define the EndDates, which then gets written back into the db.

Hope this helps! Please let us know if you have more questions, or mark this post as solution complete if it resolves your query.

AbhilashR · Accepted Answer

Hi @Anastasio_Theohari - how about we pass distinct ID values from table2 into table1's SQL where clause, to only bring the corresponding latest records for these iD's? That way you limit the universe of data being pulled into Alteryx.

Alternatively, you could leverage in-DB tools and pass table2 ID's into table1, and fetch the corresponding table1 records for more downstream analysis.