Dynamic Query and Save name File.

Question

Hello everyone!

I'm still learning this complex software and I've encountered a problem.

Since I'm used to create little programs in Python, bash to automate SQL queries and save Data Bases in a close and controlled environment, I'm trying to do the same in Alteryx.

for example:

- Depending on what Week I am (example... Week = 03, Year=2023, Month = 04) I need to download my information from the last week, in this case, Week = 02 same Month and Year.

I want to dynamically change the Query inside "Connect to DB" Tool. since I have something like:

(That is just an example)

Is there any way to automate the change of that specific text in the Where Clausure "20230402" depending on the week that I am?

It might be simple to change every Morning just those numbers and run it, but I want it to escalate it to a bigger workflow where there are multiples downloads.

As well for the output files names.

That, if you downloaded Week 02, the file name will automatically change to XXXX_2023_04_02.yxdb

I've looking on some old post but doesn't quite explain how to do it properly using "Connect to DB" Tool or if there is another option.

Thanks in advance!

DataNath · Accepted Answer

Hey @BetoZM, if you want to use regular Alteryx tools then you can just use the standard Input Data tool and point it towards the same DB & table. You can then go into the SQL editor and stick your query there.

In terms of updating your filter, could you not dynamically do this based on today's date? My thoughts are something like:

WHERE WK.ID_WEEK Week = TO_CHAR(SYSDATE,'YYYYMM') || LPAD(TO_CHAR(SYSDATE,'W'),2,'0')

That way, you'll never have to edit this - it will just run automatically based on the current date. I've used Oracle syntax there so you'll need to adapt this to your own DB's flavour of SQL, but hopefully the concept helps.

In terms of an output, you would need to make this in a Formula tool. Again, I'd use today's date - DateTimeToday() in Alteryx - in order to dynamically generate each part of this. There's not as much support for weeks so we need to do a bit of our own calculating but something along these lines should get you close to what you're after:

'_'+
DateTimeFormat(DateTimeToday(),'%Y_%m')
+'_'+
PadLeft(ToString(Ceil(DateTimeDay(DateTimeToday())/7)),2,'0')

To add this to your FileName, just set up your Output Data tool as normal, choosing a name etc. However, in the bottom section you're going to want to 'Take File/Table Name From Field' and 'Append Suffix to File/Table Name' and select the field you've just generated in your Formula expression:

Now when you run the workflow, you'll get your file generated with your desired format suffixed to the end:

Really hope this helps. I've attached the workflow used above for you to look over but please feel free to shout up if you have any further questions!

DataNath · Accepted Answer

Hey @BetoZM, if I'm understanding your question right then you can do this with the Dynamic Input tool. Here's a simple example again, but you can use the same concept for yours.

In my Dynamic Input tool, I'll set up my database connection & table and write out the initial SQL query (in the 'Input Data Source Template' configuration), just as we did in our regular Input Data tool:

Then, going into the input of this tool, I'll plug in my Excel which states the row number that I'll use to update my SQL where clause:

Then I can go back to the Dynamic Input tool and select that I want to update the SQL where clause dynamically:

This will bring up the following configuration window, where we choose the SQL clause to update, specify which part of that we want to update and which field we'll use:

Now in your case, as you have a load of rows, you'll want to first Filter your Excel file between the Input Data tool and the Dynamic Tool, with an expression that isolates the row relating to today's date (or whichever date you want to dynamically use).

Again hope this helps but please feel free to shout up if not!