Dynamic Filter(s) in SQL and Naming Output

[ckuhn003]

New to Alteryx and seeking best practices and ideas on how to accomplish the following.

I have an existing Oracle SQL statement that I want to copy/paste into  Alteryx and run each month.

1. What is the best way to deal with changing the date range each month (i.e. WHERE DATE BETWEEN {ts '2022-12-01 00:00:00.000'} AND {ts '2022-12-31 00:00:00.000'}?

2. What is the best way to run this query multiple times with a list of different filter values (i.e. Location = 'MIA', Location = 'SEA', etc...). These values could change depending on if there are new locations added or no available data for that specific location.

3. Can I name the output file using the Date Range Used and the Location value selected in Step 2.

Thank you in advance!

[DanM]

@ckuhn003 without seeing sample data or what you've tried to do in Alteryx so for the Community will only be able to guess your level in Alteryx and how to help.

Here are some suggestions:

It really depends on what you are attempting to accomplish. If you nee to be able to adjust date ranges manually each month, then you you can either setup a filter tool to filter out the date range you need. That is the simplest way.

If you have to use different dates for different locations you can build multiple filters, one to filter the location and then one to filter the date. You would have to update the workflow each time and run. Not exactly proficient but it would work.

The most dynamic way would be to build an app that allow you to feed a batch macro. The app would consist of your locations to choose and a field for you to add the date range. You would have to run it for each date range, but I'd be surprised if you couldn't figure out a way to make that automated further.

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

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

Hope that gets you going.

[ckuhn003]

Thank you for the response. Yes, each month I'll need to adjust the date range. The data will be queried by an Input Tool connecting to an Oracle Database using a SQL statement.  I'm not sure something like a Filter Tool and/or an App would be able to modify the 'Where' clause to update the Date Range. Is that a fair statement?

[silverburst]

have you considered the dynamic input tool?

Tool Mastery | Dynamic Input entrée de données entrée dynamique eingabedaten dynamische eingabe (alt...
Modifying SQL Query using the Dynamic Input Tool: - Alteryx Community entrée de données entrée dynam...

you can create a formula to change the dates and your SQL 

Not sure if this is of use but thought I'd suggest it. I just did something similar where it pulls T-5 days based on runtime automatically

[ckuhn003]

yes, I've been playing around with the dynamic input tool and it allows me to update the Where clause when it's a simple 'column x = 'value' but it doesn't recognize a 'between' criteria (i.e. MVMT_DT BETWEEN {ts '2022-12-01 00:00:00.000'} AND {ts '2022-12-31 00:00:00.000'}. Is this possible using the dynamic input tool or do I have to use some other function within Alteryx?

[silverburst]

Can you try the replace a specific  string option and create a text with the requisite SQL?