Analytic App to Create Custom SQL Query

[hboini]

Hi,

I am trying to create an analytic app in which the user can enter dates, choose from a drop down, and check box and then have this update a SQL query and produce an excel file.

I am not having any luck and the error messages I am getting are not providing me with enough information on what to troubleshoot next. I appreciate any help. Please see attached screenshots below.

WorkflowRunning as Analytic AppErrors1st tool configuration

[Treyson]

There could be quite a few things happening here, but let's look at the immediate.

You are replacing -9999 and 7777 in your query, but those are numeric values and you are placing dates in their stead, which act as strings. Your query has no single quotes around the queried value, but in SQL, you will need those.

So it needs to look like:  cast(Field as date) = '2018-01-01' 

It currently looks like: cast(Field as date) = 2018-01-01  <-- This will fail

Change these and report back your findings

[hboini]

Thanks for responding Treyson!

I simplified the workflow and kept only the date inputs. 

I also input some dates and asked for my update tool to replace the 2018-01-01 and 2018-01-31 so that the quotations still remain. 

I chose the dates 2018-06-11 and 2018-06-15 in the interface

However, the final output file gave data from 1/1/2018 to 1/31/2018 and not the dates selected.  This makes me feel that the replace a string is not happening. 

I went back to using 9999 and 8888 in the query and kept the quotations.

However, this time the excel file produced no rows of data.  This makes sense if the query ran as it original was as there are no dates 9999 and 8888 and thus no rows returned.  It appears the main issue is with the Action Tool not actually updating and replacing any strings. 

[Treyson · Accepted solution]

I am looking at your setup and it seems that you have selected the action tool to adjust where "@fileformat - value = "23"". There is no 9999 in that so it doesn't have anything to replace. Check to make sure the action tool is looking at the correct thing to do the work on. it should be the option 'File - option: (your query)'

[hboini]

Can you please show me with screenshots where I can adjust the Action Tool configuration?  The only two places I have options are at the top drop down and the bottom replace a string field box.  I am not able to click on the area where it says @FileFormat - value="23" to adjust it.

[Treyson]

I sent you my phone number in the private messages box, call me and I will help clarify things a little. You are right there.

[jsadove]

I  have a very similar issue and nothing I try works. 

I have a sql query where i have to use a particular date in several places of the query.

I would like to have this change with input. In other words (see attached picture), I want to replace 2018-05-31 in all places it appears in the query with the user input. 

This seems so simple and common, but I find no answer anywhere and all the  variations I've tried fail.

Any and all help appreciated. 

Thanks, Joe

[Treyson]

You are going to kick yourself but if you look at that setup you have the field above selected in grey. The action tool is looking at the cachecosmeticname instead of the query. You gotta be careful here with clicking around. It should be better about that stuff really.

Another tip I would offer is to use the calendar interface tool instead. Less room for user error

[jdsmith777]

Thank you Treyson! Would have never guessed that. They should give it a pickbox or something...

[Treyson]

that sounds like a good "idea". I can't find anything about it over there, so we will put one together.

You can go comment and star this:

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Action-Tool-Settings-Selection-Need-to-be-Mo...

If you would like.