Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Insert and update a table

Anastasio_Theohari
8 - Asteroid

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

 

3 REPLIES 3
AbhilashR
15 - Aurora
15 - Aurora

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

dbwrite.PNG

 

Capture.PNG

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.

Anastasio_Theohari
8 - Asteroid

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

AbhilashR
15 - Aurora
15 - Aurora

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.  

Labels