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

why the outcome data is different between "in Database" and "not in database"

ll1100000
8 - Asteroid

I am trying to pull out all the date that smaller than Today's date

Here is the SQL that I am using which works perfectly..

 

SELECT *

FROM CA_AB_CM.CALENDAR_DAY

WHERE Fiscal_Date < DATE

AND Fiscal_year = 18

 

When I try to pull out the same data from Alteryx under the "in-database" function, here is what I keyed in in the "filter" function and the outcome data is exactly same as the above SQL query

"Fiscal_DATE" < date

"FISCAL_YEAR" = 18

 

However, when I try to pull out the same data not in "in-database" , the result is DIFFERENT...

Here are the data type

Fiscal_Date  === Date

Fiscal_YEAR === Int 16

When I try to key in this query into the filter function, it tells me "Parse Error at Char(20)  Unknow Variable "date"

[Fiscal_DATE] <= date

[FISCAL_YEAR] = 18

It seems like Alteryx can't recognize the date so it bring all the dates out.....

 

Although when I use the Basic Filter function by selecting [Fiscal_DATE] , it gives me the calendar for me to select the date that I want and I can select today's date which will give me the same result.

 

However I want to use a formula to always pick up any date smaller than today's date without adjust it...

 

Why the same formula works differently under "in-database" and outside databse?

 

My Alteryx version is 10.4

 

Thank you.

4 REPLIES 4
DanS
9 - Comet

Try using :

 

[Fiscal_DATE] <= DateTimeNow() 

AND [Fiscal_Year] = 18

ll1100000
8 - Asteroid

Hi DanS;

Thank you for your help. This formula works!!!

[Fiscal_DATE] <= DateTimeNow() 

AND [Fiscal_Year] = 18

 

Now, I want to add another filter to get the 1st day of the current month...

I tried your formula that you replied to me in another post, but I continue getting syntax error" expected something between '(' and')'

 

here is how I mimic your formula

Datetimetrim([Fiscal_DATE], 'month')=Datetimetrim(datetimefirstofmonth(), 'month')

 

 

 

DanS
9 - Comet

Putting this into the Filter Tool Custom Filter should work for you:

[Date] = DateTimeFormat(DateTimeFirstOfMonth(), "%Y-%m-%d")
ll1100000
8 - Asteroid

Hi Dans  @DanS

I think "date" is always the nightmare for me when using Alteryx....

 

I try to set up the system automatically pull out yesterday's data everyday by using this formula

[Date] = date-1   

Again, this works great when using it under "in database", but doesn't wok under "not in database"

 

So I tried a few solution as below, but none of them works......Do you have any idea to enter the proper formula ???

[date]=datetimetoday()-1

Error: invalid type in subtraction operator

 

[Date] =  DateTimeFormat(tostring(tonumber(datetimetoday())-1), "%y-%m-%d") 

This one runs through successfully and didn't pops up any error, but didn't generate any output data..

 

Thank you.

Labels