ETL SQL Statement

nj-John-mirc

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)
datapoint1	oct 18 2020	if 'column1' = 'datapoint1'  then 'column2'  minus 1 mth from the datafield in Col2'
datapoint2	oct 18 2020	if 'column1' = 'datapoint2'  then 'column2'  minus 2 mths from the datafield in Col2'

 

result...

COLUMN1(txt)	COLUMN2(date)	COLUMN3(date)
datapoint1	oct 18 2020	Sept 2020
datapoint2	oct 18 2020	August 2020

MarkSnodgrass

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. 

MarkSnodgrass

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. 

nj-John-mirc

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

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

codysmith

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