In-db filter on dates

Question

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!

apathetichell · Answer

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.

DataNath · Answer

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.

Filter dates depending on weekday.yxmd

Felipe_Ribeir0 · Answer

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.