Workbench Filter Dataset on a Year of a Date

DMJerry
DMJerry Member
in Workbench

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.

 

Capture.JPGAny help would be amazing.

Comments

  • guitarhero23
    guitarhero23 Contributor

    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?


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Unknown
    Unknown

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

  • guitarhero23
    guitarhero23 Contributor

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


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • guitarhero23
    guitarhero23 Contributor

    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


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Unknown
    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
    guitarhero23 Contributor

    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.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Unknown
    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 >=

    Capture.JPG

    .

  • Unknown
    Unknown

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

  • guitarhero23
    guitarhero23 Contributor

    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.

     

    Dojo-Help-99.jpg

     

     

     

     


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Unknown
    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.

    Capture1.JPGCapture3.JPG

  • guitarhero23
    guitarhero23 Contributor

    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.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • DMJerry
    DMJerry Member

    @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.

This discussion has been closed.

Categories