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

how to delete selective records in target table using alteryx?

bhardwajankita07
8 - Asteroid

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

 

10 REPLIES 10
s_pichaipillai
12 - Quasar

Hi Ankita,

 

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

 

Pre.PNG

s_pichaipillai
12 - Quasar

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
8 - Asteroid

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
8 - Asteroid

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
12 - Quasar

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

Block.PNG

bhardwajankita07
8 - Asteroid

Hey thanks :), I will try this ..

bhardwajankita07
8 - Asteroid

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
12 - Quasar

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

something like below or attached sample

 

Cont Parm.PNG

s_pichaipillai
12 - Quasar

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

Labels