Dynamically change the the query

Question

Hello All,

I've a query like below

SELECT PRD_DATE, 
Count(DISTINCT PRD_AMT), Sum(PRD_TAX) AS TAX
FROM PRODUCT
WHERE REL_DATE=  '2023-06-25' AND PROD_ID = '123' 
AND PRD_DATE<=  '2023-06-25' AND  PRD_DATE >=  '2023-06-19'  (this should be 7 days back from REL_DATE)
GROUP BY 1

Here I want to update the REL_DATE and PRD_DATE dynamically (updating any specific date other then '2023-06-25') using the excel file so that I can also schedule it in the server.

Please advise.

NOTE - I can't use current date as there are chances that the data might not be available for that date. Hence I want to update the particular date

BR

Swapsingh

swapsingh2712 · Answer

@Hammad_Rashid Thank you for your response. Actually I'm not able to find the option set parameter in my alteryx. I'm using 2021.1 version. Do you have any example workflow which I can follow for my reference

Hammad_Rashid · Answer

To dynamically update the REL_DATE and PRD_DATE in your query based on an Excel file, you can use Alteryx's dynamic input tools and workflow parameters. Here are the general steps to achieve this:

* Input Tool for Excel File:

* Use the Input Data tool to read the Excel file containing the dates you want to use for REL_DATE and PRD_DATE.
* Configure the tool to read the relevant columns from the Excel file.

* Workflow Parameters:

* Create workflow parameters to hold the values for REL_DATE and PRD_DATE.
* Go to the Workflow tab, click on the "Options" icon, and select "Workflow Configuration."
* In the Workflow Configuration window, go to the Parameters tab, and add parameters for REL_DATE and PRD_DATE. Set their data types appropriately.

* Filter and Formula Tools:

* Use the Filter tool to filter the records from the Excel file to get the specific row that you want to use for the dynamic dates.
* Use the Formula tool to convert the filtered date values to the desired format if needed.

* Update Query with Dynamic Dates:

* Use a Formula tool to create a new column with the dynamic date for REL_DATE and PRD_DATE.
* Update your query to use these dynamic dates.

Example Formula for Dynamic Dates:

// Assuming the dynamic date columns in the Excel file are named 'Dynamic_REL_DATE' and 'Dynamic_PRD_DATE' REL_DATE = [Dynamic_REL_DATE]; PRD_DATE = DateTimeAdd([Dynamic_REL_DATE], -7, "days"); // Subtract 7 days from REL_DATE
* Update SQL Query:* Modify your SQL query to use the dynamic dates in the WHERE clause.
* Use the parameters in your SQL query.

Modified SQL Query:

SELECT PRD_DATE, Count(DISTINCT PRD_AMT) AS Distinct_PRD_AMT, Sum(PRD_TAX) AS TAX FROM PRODUCT WHERE REL_DATE = @REL_DATE AND PROD_ID = '123' AND PRD_DATE <= @REL_DATE AND PRD_DATE >= DateTimeAdd(@REL_DATE, -7, 'days') -- 7 days back from REL_DATE GROUP BY 1;
* Output Tool:* Configure the Output Data tool to save the results wherever you need them.

Now, when you run the workflow, you can provide the dynamic dates using the Excel file, and the SQL query will be updated accordingly. This approach allows you to schedule the workflow on the server, and each time it runs, it dynamically adjusts the dates based on the values in the Excel file.

rzdodson · Answer

@swapsingh2712 I think it is possible, assuming that the Excel file is stored in a way that the Server-based workflow can retrieve. Although, I don't think this would solve your dilemma necessarily, as the SQL query would functionally run before Alteryx has the ability to influence the results of your query.

If you do not want to utilize interface tools at all, there are two other options you can take (at the minimum): using a Find and Replace/Filter combination, or 2) an Append/Filter option.

Option 1: Find and Replace/Filter combination

This option would save your SQL query as a string field in Alteryx. From there, you can add a Find and Replace tool that will pass in the Start Date and End Dates and have it change those components of your SQL query. Then, you'll have to develop a batch macro that will pass in the SQL query syntax in order to update the query's script before rendering its results. If you had multiple queries that needed to be run, this can be a useful approach. You would functionally be able to create a batch process to run multiple SQL queries from one workflow, rendering results to where you need them saved. Additionally, you can have each query's output pass through a Filter tool that tests your date field against the established range of your Start and End Date.

Option Two: Append/Filter option

I would recommend this option if the query's initial date range doesn't have to change, but the extract you are pulling from a specific data set changes. Good example would be you want to output all records over the last two years; but, today's analysis only needs to look at the previous quarter. To set this up, you need to create that Excel file that has its Start Date and End Date values in there. Then you combine the results of your SQL query with your Excel file using an Append tool. Similar to the above, you would then add a Filter tool to filter for records within a particular range.

Hope that helps, @swapsingh2712!