Workbench Filter Dataset on a Year of a Date

Hi,

I am trying to bring in some ODBC datasets, but only want workbench to pull in anything that is for the current year. I have invoice date in my dataset as a column that I want to use the following transformation to isolate only invoices for this year. See screenshot below.

I know that I can use redshift/SQL/Magic to do this but wanted to try and do it on workbench to avoid having to eat of ETL time.

Any help would be amazing.

Find more posts tagged with

Comments

guitarhero23

You have the right idea, what about that didn't work? What flavor of SQL is this being pulled from? If you have access to the database build your query in there and check your work, if it runs in the DB then just copy and paste that into Workbench. If it didn't work in your DB then you'll want to figure out the right way to do that.

In SQL Server this should work

SELECT * FROM "ivhsth - Invoice history Hdr"

WHERE COMPANY = '01' AND YEAR(IV_DATE) = YEAR(GETDATE())

This appears similar to what you tried, what didn't work for you?

unknown

Hi, Yeah it is not working. Wondering if any DOMO admins know how this should work in workbench?

guitarhero23

What's not working? You were able to run it in the DB successfully but not in WB? What SQL are you using?

guitarhero23

I just did this in my Workbench connecting to SQL Server, using the same WHERE clause as I put in my answer against my own DB and it successfully transmitted to Domo

unknown

Hi,

Did you do it in workbench or did you do it in SQL AFTER workbench pulled in the dataset to transform? I can do it in SQL/redshift/MAGIC ONLY once the raw tables is imported into DOMO but I want to do it within workbench.

guitarhero23

I followed the process I wrote about before, I wrote the query in my SQL Server database, once I got it working I pasted it into Workbench under the "Processing" section in data (where you put your query) and ran it from Workbench so that it came into Domo with only the current year, not dataflows.

unknown

This is what I get. I've tried year(today) or = and lots of other items. You on workbench 5?

[09.04.19 12:51:28 PM] One or more errors occurred.
ERROR [37000] [PxPlus][ODBC Driver]Expected lexical element not found: =, <>, <, <=, >, or >=

unknown

Mind showing me your workbench screen shot in the DBQE? Database query editor?

guitarhero23

Hi can you answer this first. Are you connecting to SQL Server? MySQL? Oracle? PostgreSQL? Other? Depending on the type of SQL you're using maybe the way I wrote it won't work the way I've been writing it is for SQL Server but I'm sure others use same "GETDATE()" methods, but you'd have to check.

I've blanked out some info but trust me that the data is there.

unknown

Hi,

This is not being transformed in anything aside from workbench. Whatever SQL that the database query editor, in workbench, is using is the one. I can run it with the company but when I use date and year it never works. This is coming straight from a PXP ODBC connection and pulling raw tables and trying to parse out the data for faster run times.

guitarhero23

What is PXP? Is that the kind of database it is? It's possible neither GETDATE() nor YEAR() is a valid function. If PXP is not the type of database it is then please let me know what it is. Use the date functions applicable to your database. Is this PXP ODBC driver the only one you can use to connect to your DB? I assume it's some special case if you need some special driver.

DMJerry

@guitarhero23 yes this is the only format we can use. I figured it out but it isn't as dynamic as I would like it to be.