PreSQL Statement for deleting certain rows prior to append in Output Data tool

[dralban]

I need to append rows to a table on daily basis, but delete all current month rows. The rows being appended have a year and month columns, and I also have an Excel file imported that has year and month. Is there a way to write a PreSQL statement that would only delete the current month? As in:

delete from TABLE where Y = <GIVEN YEAR> and M = <GIVEN MONTH>

I do have another process that uses a Text Box as a runtime user input, and I am able to do the following:

delete from TABLE where Y = %Question.Year% and M = %Question.Month%

And that works great, but I'm not sure how I can reference it otherwise...

[mceleavey]

Hi @dralban ,

You can write your pre-SQL statement in the Pre-SQL statement section of the output tool. The dynamic sections, such as the month, replace with ZZZZZ for example. Then, wrap the entire thing in a macro and feed this value in dynamically as a control parameter:

Configure your action tool to overwrite the value you want to be dynamic:

This way you can determine in your Excel loads, and via formulae tools the values of the dynamic sections. These can then be fed into the macro.

I hope this helps.

M.

[dralban]

Thanks for a quick answer @mceleavey 

I don't have a lot of experience with macros unfortunately. I understand something like this:

And that works great. Would you mind showing me how to replace the text boxes with a macro that takes an Excel file as input, grabs Y and M from there, and uses those values to update the PreSQL statement dynamically?

[mceleavey]

Hey @dralban , no problem.

You need to replace the input with a macro input.

Replace the text inputs with control parameters.

Save that as a macro. Open a new workflow, add the macro you've just saved (you'll need to save it in your macro folder, as set in Options->User Settings->Edit User settings ->Macros tab).

Then you can load in your Excel files and feed them into the macro. Then isolate the dynamic value (group by if needed) and feed into the control parameters.

I would also recommend working through the Introduction to Macros section on the community:

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros 

M.