Mssql database and alteryx. How do stage my data correctly.

Question

Hi

Im working on a future model at work. And i'm not quite sure how to do this, because the solution I have seems bad.

Basicly I want to take input data and store it to a mssql database. Simple enough.

I have 2 tables.

Table 1.:
Orders
id - primary key, identity 1
parcelnumberpurchase

creditornumber

shipmentyear

bookinglineid

Table 2.:
OrderDetails

id - primary key, identity 1

parcelnumberpurchaseid FK - table 1 parcelnumberpurchase

amount

quantity

currency

We do the same base data - YTD incl. new month, every month. But we often change something in our models, so I want 1. check if the parcelnumberpurchase excist on the same creditor the same shipmentyear.
If not, then insert it.

1.: Then I want that id on my 2. table. But do I need to query my table 1, and join the id on table 2?

2.: If the data on a parcelnumber changes - i would like to delete the data from table 2 for that parcelnumber and add the new one. Can I do that?

Thanks you very much

DavidSta · Answer

Hi @Hamder83,

for your 1. question: As the ID is generated most likely by the database itself you need to query the database again to get this information.

2.: You can create an Alteryx workflow to identify the data, but the output Tools don't support something like a delete operation. In this case you need to play with Post-SQL scripts (https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Delete-Records-Using-a-Post-SQL-Statement-Macro/ta-p/17370) You could achieve something similar with an additional column like "delete_flag" to keep all records alive but still be able to query only the active rows. Or simply use an update for the 2nd table as well.

Normally an operation like this is directly handled through the database with foreign key constraints to not have inconsistencies in your dataset which happens when not running the workflow.

Best regards,

David