Alteryx Designer

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

Dynamic Date Formula in InputData

Highlighted
5 - Atom

Hello,

I have a Dynamic Date Formula that works as a Filter, but can't get it to work on input data.  I'm trying to limit the records that get pulled in from source data for performance.

 

Ideally, i'd like to just the records for the Max Date.  using Filter Tool, I have been able to get the below to work.  Can I use a Max date function in input, or could this formula be used in Input?

 

[AsOfDate1] = ToDate(DateTimeAdd(DateTimeToday(), -1, "days"))

 

Thanks

Highlighted
8 - Asteroid

Hello @DG_Tampa 

If your datasource is a DB you can use the sql query to limit the number of records , you can write the query in the sql editor tab of the input tool.

Tyrion_Lannister_0-1588613562418.png

 If your datasource is csv/excel/yxdb then the files will be of small size which alteryx can easily read without any issues and you can limit the size after the input tool using a formula or filter tool. 

You can enable profiling and see which tools are taking more time to process the records.

Tyrion_Lannister_1-1588613811305.png

 

Thanks

 

 

Highlighted
5 - Atom

Hello,

thanks for the feedback.  I was in sql editor on the I input, which is linked to a database.  The problem I have is, all the formulas i'm trying is giving me errors.  I tried linking the database, with no filters or manipulation, then created a "Filter" using the dynamic formula.  I then copied that dynamic formula into my input (sql editor), but continue to get errors.  I'm not sure what is going wrong at this point.  While this particular workflow would work with either method, i'd like to utilizes this function on much larger workflows i'll be building)..note - i'm about a week in with my Alteryx knowledge, but am proficient in sql, excel, access, etc...

 

I will also try the profiling to see where my hangups are.

Highlighted
8 - Asteroid

try your query in SSMS/SQL assistant and if it works fine then copy it in the SQL Editor tab of the input tool,it should work

Highlighted
5 - Atom

Hi @Tyrion_Lannister

 

Unfortunately, I don't have a way to use SSMS for this particular database.  I just thought it was strange that the filter query could not be pasted into the InputData connection as a select statement.  The field is in date format, but I just can't get this function to work in this case.

Highlighted
5 - Atom

For those interested, I found a formula that worked.

 

 

[DATE\ = CURRENT_DATE -1     

Labels