Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Analytic App to Create Custom SQL Query

hboini
5 - Atom

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.

 

WorkflowWorkflowRunning as Analytic AppRunning as Analytic AppErrorsErrors1st tool configuration1st tool configuration

9 REPLIES 9
Treyson
13 - Pulsar
13 - Pulsar

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

Treyson Marks
Senior Analytics Engineer
hboini
5 - Atom

Thanks for responding Treyson!

 

I simplified the workflow and kept only the date inputs. 

01.JPG

 

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. 

 

02.JPG

 

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

 

05.JPG

 

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.

 

03.JPG04.JPG

 

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
13 - Pulsar
13 - Pulsar

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)'

Treyson Marks
Senior Analytics Engineer
hboini
5 - Atom

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.

 

06.JPG

Treyson
13 - Pulsar
13 - Pulsar

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.

Treyson Marks
Senior Analytics Engineer
jsadove
6 - Meteoroid

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
13 - Pulsar
13 - Pulsar
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
Treyson Marks
Senior Analytics Engineer
jdsmith777
7 - Meteor

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

Treyson
13 - Pulsar
13 - Pulsar

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.

Treyson Marks
Senior Analytics Engineer
Labels