In-db filter on dates

[fborgmann]

Hello all,

I'm trying to implement a filter, so that my connect in db tool queries either the last 3 or the last 4 days depending on the weekday, the Workflow is executed on. 

My current attempt looks like this:

 

Unfortunatley I'm getting the following error message: 

Does anybody out there now how to do this? 

Thanks alot for help!

[Felipe_Ribeir0]

Hi @fborgmann 

When you are using IN DB components, you must use the syntax of your DB to write the queries.

For example using SQL Server the equivalent for that Alteryx formula is:

Alteryx	SQL Server
DateTimeFormat(DatetimeToday(), "%a") = "Mon"	DATEPART(weekday, GETDATE()) = 2

So, write this query as if it would be written inside your db frontend interface.

[fborgmann]

First off, thanks for trying to help!

I just changed the query as you said and now I'm getting this error: 

Im connecting to an apache hive server

[Felipe_Ribeir0]

Hi @fborgmann 

It was just one example of syntax if you were using SQL Server.

If you want/need to use the in db components:

1)You must know which is the DB that you are connecting too.

2)You must see how to write that full statement using this DB.

OR if you want/can to avoid using them now:

1)try to data stream out all the dataset from your db to Alteryx

2)And use Alteryx common components, that you already know the syntax, to write this if.

[DataNath]

Hey @fborgmann, here's how I'd approach this task. As mentioned previously, when using In-DB tools, we need to use the SQL syntax of the database you're using. Mine is based on MS SQL Server and so your syntax/expressions may differ slightly. However, hopefully this can at least demonstrate the concept.

Firstly, I've just loaded in a handful of dummy dates:

Whilst we could go directly to the filter, I thought I'd include the formula step beforehand to help show what these expression return and break down my approach, as the filter is essentially a consolidation of these:

Expression 1:

This is your 'if Monday then -4 else -3' part... Once more, you'll notice the syntax is different as it's written in SQL, but this is achieving the same thing.

Expression 2:

This is simply retrieving today's date; the equivalent of DateTimeToday() in Alteryx.

Expression 3:

Combining the two - when the weekday is Monday, take 4 days away from today's date, otherwise take away 3 days.

When we stick a browse on that, we can see the results of these 3 expressions:

1) Today is a Thursday, so we get the -3 value

2) Today's date (GETDATE() also returns time which we'll deal with in the filter) is 2022-10-20

3) Because today is Thursday (-3), we then get 2022-10-17 when applying this date difference (DATEADD() in SQL = DateTimeAdd() in Alteryx)

So, moving to the filter... In this expression, we basically want to tell Alteryx: Bring back all dates that are within -3 days if it's not a Monday, and within -4 days if it is a Monday, which we can do with the following:

The additional CONVERT() function here is used to treat the result of the GETDATE() as the date part only, as the filter won't work properly trying to compare our "Date" field (of date data type), to the resulting DateTime data type from GETDATE().

The results? True anchor brings back all days within the last -3 because today is a Thursday:

False, everything else:

Really hope this helps but please do feel free to shout up if not! I've attached my workflow for you to look over. However, the DB connections will obviously break and you'll need to use your own.

[apathetichell]

That's a formula expression - not a Filter. Formula In-DB uses CASE WHEN ... THEN ... ELSE ... END. Filter is a an embedded Boolean based operator.  You do not use an IF statement with a filter - it applies an IF automatically to the statement. Formula creates new fields.