Dynamic Query and Save name File.

[BetoZM]

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 solution]

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!

[BetoZM]

I didn't know that this could turn out a little bit complex in Alteryx on formulas.

Thanks for every detail on the Workflow, sure it will help me a lot to change dynamically the names of the output files!

On the other hand, your solution to automate SQL query helped me!

Do you think that you could help me with one last thing?

Here we use 2 different calendars, one that is that you and I know for life and other one that uses "Periods".
For example, I have an Excel with every single date of all 2023, in those dates I have a column to know what Period it is.

As you can see Period 01 takes some of the days from February. and this leads to another result on the final total when we talk at least Half Year using only Periods.

Is there any way to do almost the same approach that you just did in SQL but taking this Excel file and use it to modify the same SQL Query? (Is the same DB, just another table that stores the Period ranges [VVDC.ID_PERIOD = 20230102] Where 2023 is Year, 01 is P01 and 02 is W02) 

Thanks in advance!

[DataNath · Accepted solution]

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!

[BetoZM]

Sorry for the long period of time without any update.

I just came here to say that everything you suggested helped me a lot and solved the Dynamic Queries problem.

Thank you so much!