ETL SQL Statement

nj-John-mirc
nj-John-mirc Member
edited March 2023 in SQL DataFlows

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    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. 

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • nj-John-mirc
    nj-John-mirc Member

    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 Coach

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • codysmith
    codysmith Domo Employee

    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

Categories