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
Solved! Go to Solution.
@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.
Hello ,
Thank you for your answer but lets assume that we have a large data set and we do not know which rows has different ages based on a specific ID,so in our case the filter ID='S7' is working for this example but not for large volumn of data,it is something we want to look up,otherwise we will have dublicates..
Thank you for your help...
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.