Alteryx App with user input for In-DB Query

[BonusCup]

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?

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.

The error I receive from the Gallery:

Thanks in advance

[danilang]

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]

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

[danilang]

Hi @BonusCup 

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

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]

@danilang 

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

[apathetichell]

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]

@BonusCup 

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

Dan

[BonusCup]

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

@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.

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.  

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!

[saralisset]

Hi! I am new to Alteryx, but I have been requested to build something similar. Currently my report takes forever to run and this is because I pass the filters after doing select ALL. Can you please share screenshot of the right part of your workflow to give me an idea? 

This is my workflow: