how to delete selective records in target table using alteryx?

[bhardwajankita07]

Hi Team,

I want to perform incremental load in redshift using alteryx, which i am not able to achive from  "Update;Insert if new" option in output_data tool.

So while inserting the data into the target table, I want to delete the already existing records and append the new and updated records to the target.

So can you just tell me how to delete selective records from the target table, because when I use delete option in Output tool it delete the complete table.

Regards,

Ankita Bhardwaj

[s_pichaipillai · Accepted solution]

Hi Ankita,

did you try with the Pre SQL Option in the output tool?

[s_pichaipillai]

The Issue with Pre SQL is it will be exceuted when the workflow starts

here is a nice use of Dynamic Input tool where you can perform incremental load(Update;Insert if new)

see the below link

http://blog.concentra.co.uk/2015/02/23/alteryx-perform-dynamic-incremental-data-extracts/ 

[bhardwajankita07]

Thanks for your valuable input, but thing is that I want to use dynamic Delete query in Pre SQL statement.

And the ids which are required to be deleted from table X , are kept in a table Y, and this table Y is also created in the same flow.

So kindly tell me is there any why to set the target load order (sequence of data load), so that after loading the table in Y then we load in X (using this pre sql delete query "delete from X where id in (select id from y)"). 

Regards,

Ankita Bhardwaj

[bhardwajankita07]

Ya i tried doing this as well, and this is working fine with rest of the datbase but not with Amazon redshift , as in redshift primary key logic is unlike other database it allows duplicate records even though if the column is declared as primary key.

Hence that is the response this "Update; Insert if new" doesn’t work in redshift and i am going for the pre sql delete logic

[s_pichaipillai]

Ahh that's sad that its not working for redshift

if you want to control the order of execution then use the Block until done to Load Table Y then Load Table X with pre SQL.

But i still suspect the presql will be called when the workflow start executing (i still remember Alteryx team said that)

something like below

[bhardwajankita07]

Hey thanks :), I will try this ..

[bhardwajankita07]

Can you also tell me how to use mapping variable in a macro containing output_tool.

As I want to use mapping variable in the Pre sql statement (delete  from [v_schema].[v_table]  ) in macro.

[s_pichaipillai]

Use control Param Interface then use the Macro as your Destination output

something like below or attached sample

[s_pichaipillai]

and i would suggest to test this method end to end :-)