ETL SQL Statement

Hi,

 

I'm not familiar with SQL and wondering if someone can help with a SQL statement in an ETL that would solve the below(hope it makes sense)...

 

COLUMN1(txt)COLUMN2(date)COLUMN3(date)
datapoint1oct 18 2020

if 'column1' = 'datapoint1'

 then 'column2'  minus 1 mth from the datafield in Col2'

datapoint2oct 18 2020

if 'column1' = 'datapoint2'

 then 'column2'  minus 2 mths from the datafield in Col2'

 

result...

COLUMN1(txt)COLUMN2(date)COLUMN3(date)
datapoint1oct 18 2020Sept 2020
datapoint2oct 18 2020August 2020

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    Accepted Answer

    If you are using MySQL, you could do this:

    SELECT Column1, Column2,
    CASE WHEN column1 = 'datapoint1' THEN DATE_SUB(column2, INTERVAL 1 MONTH)
    WHEN column1 = 'datapoint2' THEN DATE_SUB(column2, INTERVAL 2 MONTH)
    END as Column3
    FROM MyTable

    You can also do this in MagicETL, which might be worth doing if you are not comfortable with SQL. You would use the Filter tile to split your data into two groups: datapoint1 and datapoint2. You would then use the Date Operations tile on both of those and use the subtract from date function in the tile and subtract 1 month for datapoint1 and 2 months for datapoint2. You would then use the Append tile to bring the datasets back together. 

    Hope this helps. 

Answers

  • Thanks Mark, it worked!  One thing - instead of a filter tile I used Group By and added the sql statement mentioned above in the Formula box.  Thanks again!

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    ... i don't know your data, but i'm not sure why you'd do it in a GROUP BY instead of a FORMULA tile.

     

    Also, instead of managing crazy math with a CASE statement, create an Interval column that says how many months to go back (i.e. 1 or 2) and then use your Formula (or GROUP BY) tile to pass the interval (number of months) as a variable.

  • Hello @nj-John-mirc ,


    I'm happy to let you know we have just released Code Block apps in our Domo Appstore. These allow customers and Domo experts such as yourself, to publish code blocks like the ones you've created for distribution in the Appstore. The publish process for the code blocks is currently in an early beta. The supported code blocks are:

    - Beast Mode

    - SQL

    - R

    - Python

    - Jupyter Notebook

    If you are interested in publishing your code blocks, let me know and we can enable it for you.

     

    Cheers,


    Cody Smith

    Director of Product, Domo