Using variables for dates in the SQL editor

[datamodeling101]

I have several hard coded SQL statements in an input data that have a WHERE clause to limit the data between two dates i.e. WHERE DateField BETWEEN '2019-01-01' AND '2019-12-31'.  

Is it possible to create a variable in place of dates in the between statement for each SQL statement in the SQL editor?  That way instead of hard coding the dates to pull different years in each statement I can just update the variable for beginning and end dates and all SQL statements will be updated?  i.d.

WHERE DateField BETWEEN [Variable1] AND [Variable2];

I'm new to Alteryx.  If there is a better way to do the above without using variables how would I go about it?

[Thableaus]

Hi @datamodeling101 

Would the Dynamic Input Tool help you with this?

Check more about it in this article.

Cheers,

[Thableaus]

@datamodeling101 

Just to be more clear, there's an option in the Dynamic Input Tool that you can update a WHERE clause with a field.

So you could calculate this field or make it dynamic according to your logic, and run the workflow to retrieve results with this WHERE clause updated.

Cheers,

[Claje · Accepted solution]

Hi,

Roughly there are three good ways that you can go about this.

Method 1 is as @Thableaus suggested using Dynamic Input, something like the below screenshot:

Another option, which I wouldn't recommend unless you need other users to be able to run the process, is an Analytic App.  These are great for letting others run the process, but a little inconvenient if you need to be able to test or debug code changes.

Finally, you can make use of User Constants.  These are set in the Workflow Configuration tab.  For example, you could create one that is "StartDate" and one that is "EndDate" and populate them.  You can then update your WHERE query to be something like:

WHERE DateField BETWEEN '%User.StartDate%' AND '%User.EndDate%'.

This will automatically be replaced on runtime, so all you have to do is change the value of the two user constants in the Workflow-Configuration tab.  This is probably the "safest" option, in that you can continue using all the tools you normally use, and it will only replace EXACTLY the data you've specified, although it is the least dynamic for future efforts, like, say, making the workflow into an App or sourcing your dates from another file or database.

[Thableaus]

@Claje 

I think the only downside about User Constants is that they are in some way, hardcoded to your workflow like a string would be in a query.

They are more useful when you use the same string over and over again in your workflow and you want to update it all at once. 

Cheers,

[datamodeling101]

The user constants was what I was looking for.  Thank you.

[Claje]

@Thableaus absolutely, and if you need a more dynamic solution, an App or Dynamic Input are the way to go, but they each have their own pros and cons that should be considered.

To be clear, all three of these options will solve the problem, and any of the three might be the best option depending on your needs :).

[roshea]

I do need to use this in an anlytical app, how can I get the variable in the SQL code? Or is there a way for the constant to be updated by a text box input or something?