ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Alteryx App with user input for In-DB Query

BonusCup
8 - Asteroid

I've created an App where a user inputs a Date, Hour and Minute which changes the default inputs in the Filter and then creates a SQL query that is used for an In-DB query.  When I run the Workflow with the default inputs the SQL gets generated correctly but when I run the App the entered date does not populate but the Hour and Time that was entered does.  Has anyone ran into this and can assist?

 

BonusCup_0-1620141164907.png

 

I created a csv file where I can check the SQL that gets generated.  Below you can see the difference when running the WF locally and then running the App from the gallery.

BonusCup_1-1620141378419.png

 

The error I receive from the Gallery:

BonusCup_0-1620141578861.png

 

 

Thanks in advance

 

danilang
17 - Castor
17 - Castor

Hi @BonusCup 

 

Check the action tools that you're using to update the date and time.  Make sure that the correct line is selected and also that you're updating with values from the proper field.

 

Dan

BonusCup
8 - Asteroid

@danilang here is what I have the Action and Filter configured to:

BonusCup_0-1620152629941.png

 

danilang
17 - Castor
17 - Castor

Hi @BonusCup 

 

Go into Interface Designer, click the Wand, enter the values in the Interface tools and press the Debug button  

 

danilang_0-1620153367162.png

 

This opens up a version of your workflow with all the values replaced in the main tools as if it had been run as an app.  Look at the tools that should have been changed.  Look at the values coming out of the True anchor of the Date filter and see if they are what you expect.

 

Dan

 

BonusCup
8 - Asteroid

@danilang 

 

Well, I was hoping I'd find something but it looks like all the filters are working correctly.

 

BonusCup_0-1620154373725.png

 

apathetichell
13 - Pulsar

From an Alteryx perspective, I assume [start date] is a date field. From a Presto perspective:

 

https://prestodb.io/docs/current/functions/datetime.html

 

needs .000 (thousandth of a second) as a trailer.

 

 

An Alteryx date/time field can have this level of detail as date/time and this could be causing an error. I'm totally unqualified to say if this could be part of your problem or not - but since your error appears to me to be in validating your timestamp - adding the .000 is worth a try.

danilang
17 - Castor
17 - Castor

@BonusCup 

 

What about the output of the final formula tool in your original image.  Are all the fields correct there?

 

Dan

BonusCup
8 - Asteroid

@danilang 

 

Sorry for the delayed response.  I debugged again and checked my query in the csv file it creates and now it shows missing date, missing entire timestamp, and missing the minutes of the last timestamp.

 

BonusCup_1-1620217486046.png

 

 

BonusCup
8 - Asteroid

@danilang 

 

I believe I have figured it out.  Initially I had just used a text input with a few dates listed in it.  I was under the assumption that the Action would just replace the date from the Input but since dates were not in that Input when it got to the filter there were no results to replace.  I solved this by creating 13 months of dates to replace.

 

BonusCup_0-1620227128552.png

BonusCup_1-1620227150624.png

 

I also created an Input for both Hour and Minute that lists out 00 - 23 and 00 - 59 that feeds into the corresponding filters for the Start and End times.  This seems to have solved my issues.  

BonusCup_2-1620227416919.png

 

Thanks for pointing me to the Interface Designer and Debug tool.  This was extremely helpful in troubleshooting.

 

@apathetichell, I also added the '.000' at the end of the timestamps and it worked both ways after fixing the above issues.

 

Thanks to you both!

 

Labels