Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Database table updates

ulrich_schumann
8 - Asteroid

We are using SQL Server database to store project status information. The database is updated until now by an external tool and we were are using the database as a source for further queries with Alteryx.

 

Now we want to get rid of the external tool and do the updates of the tables by regular Alteryx workflows.

 

For the already existing tables we have the 4 options we need to handle.

 

1. new data to be added to the existing table

2. existing data to be updated with a new value from the Alteryx workflow source data 

3. existing data is no longer in the Alteryx workflow source data but should still stay in the database table

4. existing data in the table to be deleted (having a specific flag)

 

We are receiving weekly/monthly status files with the latest project information. No history stored, projects that are completed might disappear from the source list but should stay in the database table.

 

Are there options within the Output Tool to follow the rules above? If yes, how do I implement the different scenarious into the workflow output?

 

2 REPLIES 2
dataMack
12 - Quasar

You implement those rules as decisoon points upstream of the output in your workflow, say with a filter tool, or from the left and right output on a join tool (data no longer in) and then send them to seperate Output tools. You can have as many branches off a tool as you want and as many output tools in your workflow as you want.

 

ulrich_schumann
8 - Asteroid

I that case I would need to know already within the workflow how the current data base status is to include this logic into the workflow.

 

My hope was/is that the decision what to do with the specific data set is handled directly during the database output from Alteryx.

The status files which we receive have no information what was reported in the last cycle - it is just a snapshot of the as-is-situation from now. The database on the other hand has all items that have been reported in the past with the latest status information.

 

So when running the workflow, an individual decision for each entry has to be made:

- this record is not yet available -> add a new database entry

- the record is already available -> update the fields that has changed compared to the existing database set

- record is no longer in the report available but still in the database -> do nothing

- record in the report has a delete flag -> flag record in the database as not active

 

From your answer I assume there is no way to handle this via the output tool (or any other tool), right?

Any ideas are very welcome.

Labels