Update two dates in SQL with data parameter?

Hi Everyone,

I have a Connect IN DB tool running a SQL query and outputting the data. The query works fine but contains two dates that I would like to have a date parameter built for so that a user would just enter the new date every month and run the workflow. The date is always the last business day of the prior month.

I can't seem to figure out how to do this since the date has to be in 'DD-MMM-YY' format. I attached the SQL and am hoping someone can be able to assist in helping me build a data parameter for both dates in this workflow.

Thanks in advance!

2023-04-17_14-29-25.png

Datasets

Accepted answers

DataNath

Hey @RCern, here's a quick example. I've just made this with a very simple query, but you can see I have 2 dates that I'm filtering for in the initial SQL statement:

Now, if I turn the workflow into an app which allows user-entered parameters, we can target the dates in the filters as what we want to replace by the user input. To do this I'm going to want to use a Text Box tool (for the user to type in), and then an Action tool to configure the influence on the Connect In-DB tool. Initially when you connect the Action tool, you'll want to select the 'Query' as the target, which will contain the entire thing:

So we need to reduce this down to just the part we want to switch out, i.e. the 15-Sep-17 value I have set this up with initially:

Now, as this is an app we need to choose what we show to the end user when this is ran successfully. To do so we go to the Interface Designer (Ctrl+Alt+D) and select our Browse In-DB tool:

Now when I run the app, I can enter a value and this will replace what is currently in the SQL filter and show us the output:

All comments

DataNath

Hey @RCern, here's a quick example. I've just made this with a very simple query, but you can see I have 2 dates that I'm filtering for in the initial SQL statement:

So we need to reduce this down to just the part we want to switch out, i.e. the 15-Sep-17 value I have set this up with initially:

Now, as this is an app we need to choose what we show to the end user when this is ran successfully. To do so we go to the Interface Designer (Ctrl+Alt+D) and select our Browse In-DB tool:

Now when I run the app, I can enter a value and this will replace what is currently in the SQL filter and show us the output:

RCern

Wow nice! I got it to work! LOL. Quick questions. It only produces 100 rows. There should be 40k'ish. How do I not limit it? Also, How then do I take the output results and continue to manipulate the output results like in a normal workflow?

DataNath

Awesome to hear @RCern!

If you go to the Browse In-DB, at the top of the configuration you’ll see by default it’ll cache the first 100 records but you can increase that as you wish! In terms of the manipulation, I’d build out the workflow as you intended to and just put a final Browse In-DB at the end so you show the end user the results of the entire workflow rather than just what’s coming in as we have it now.

RCern

Hmmm. Not sure I'm following. Before I put in the Text Box Input and Action Tools, the queries would all run with a fixed date and go through the Join Multiple tool and then output to the Browse tool. This is how I confirmed case counts. After the Join Multiple tool, I was planning to insert some new columns and formulas and then run the workflow again to see the output in the Browse Tool. Do I still do it that way? As of right now, the regular Browse tool doesn't display any data.

DataNath

@RCern the Join Multiple tool is normal Alteryx tool, rather than In-DB. Therefore there’s 2 options here:

Use a regular Input Data tool that just queries the same database and table. You can add a SQL query in here and edit it in the same way I demonstrated above with the Text Box & Action setup. This will stream data directly into Alteryx for you to work with the regular tools.

You can also use the Stream Data In tool in order to do what it says on the tin and flow data from your In-DB setup into normal Alteryx for you to use the standard tools.

The benefit of using just In-DB tools is that you can keep everything ‘on the database side’ - it’s essentially a visual SQL query builder where you create a workflow with regular tools and in the background, Alteryx writes a SQL script that is executed when you run the workflow. I’d look into them a bit more and your use case before deciding which is best for you to use between solely standard Alteryx, solely In-DB or even a hybrid workflow.

RCern

Thank you so so much!!!!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703