ETL - adding a field based off a Case Statement Help

user11590

Currently we have an Access Query that has an expression created to add a field with a value to our data.  Below is the expression written in MS Access

 

N/P: IIf(([DWQRYDATA_DCINVAUD]![NEW_QTY]-[DWQRYDATA_DCINVAUD]![OLD_QTY])*[DWMIRROR_IMASTER]![ICOSTP]>0,"P","N")

 

I need to add the same function to the ETL for my report I am moving over to Domo.  However I am not seeing where you can add this type of add field fucntion in the ETL setup.  

This is what I wrote for a beast mode I tested however I need this field to be embeded into the table so it can be run with other ETL's off this data.

 

CASE
WHEN `Change #`*`ICOSTP` > 0
THEN 'P'
ELSE 'N'
END

 

Can I create this type of function in an actual ETL?  Or do I need to do this via SQL (I am horrible at SQL).  I hope this can be done via ETL.

Any ideas or help is greatly appreciated!

 

 

GrantSmith

Hi @user11590 ,

 

You have three options. MagicETL, MySQL ETL or calculate in your query that pulls the data into domo.

 

Beast mode won't allow you to show that calculated field on another dataflow (it will appear on another card if you select "Share Calculation on DataSet" when creating the beast mode)

 

Magic ETL isn't very good at doing complex mathematical equations however yours seems fairly simple and should be solvable without too much hassle. 

You already have the change amount which is good as it removes an extra step but you'd need to create a new calculated field to multiple the change # by your ICOSTP field. If not you'd just need another calculator tile to calculate your change # in the ETL dataflow before the Calculator change_icostp tile.

 

 

The second option is to use a MySQL ETL which supports CASE Satements much easier but are more advanced. In this case it might be easier to utilize.

With the output dataset having the following code:

SELECT *, CASE WHEN `Change #` * `ICOSTP` > 0 then 'P' ELSE 'N' END as NP
FROM `test`

The cleanest option would be to build this into your initial query which is being pulled into Domo.

 

 

If you don't need this calculated field as part of your dataset for an input into other dataflows but only on other cards then you can go the beast mode route and make sure you select Share Calculation on DataSet

 

 

With all of that said, is your data from Access not being imported into Domo to run these reports off of? Why can't you just utilize your case statement from Access you mentioned above as part of your dataset?

 

 

Hope this helps. 

GrantSmith

Hi @user11590 ,

 

You have three options. MagicETL, MySQL ETL or calculate in your query that pulls the data into domo.

 

Beast mode won't allow you to show that calculated field on another dataflow (it will appear on another card if you select "Share Calculation on DataSet" when creating the beast mode)

 

Magic ETL isn't very good at doing complex mathematical equations however yours seems fairly simple and should be solvable without too much hassle. 

You already have the change amount which is good as it removes an extra step but you'd need to create a new calculated field to multiple the change # by your ICOSTP field. If not you'd just need another calculator tile to calculate your change # in the ETL dataflow before the Calculator change_icostp tile.

 

 

The second option is to use a MySQL ETL which supports CASE Satements much easier but are more advanced. In this case it might be easier to utilize.

With the output dataset having the following code:

SELECT *, CASE WHEN `Change #` * `ICOSTP` > 0 then 'P' ELSE 'N' END as NP
FROM `test`

The cleanest option would be to build this into your initial query which is being pulled into Domo.

 

 

If you don't need this calculated field as part of your dataset for an input into other dataflows but only on other cards then you can go the beast mode route and make sure you select Share Calculation on DataSet

 

 

With all of that said, is your data from Access not being imported into Domo to run these reports off of? Why can't you just utilize your case statement from Access you mentioned above as part of your dataset?

 

 

Hope this helps. 

jaeW_at_Onyx

Agree w/ @GrantSmith , IF you can transform the data as you bring it in via connectors or workbench, that would be the easiest way to add the column to your data.

 

Downside is, there's no visibility of that calculation to the business user (whereas MySQL or MagicETL processing steps makes the process transparent).

 

It is a pain to implement this in Magic; however, as your data grows, Magic will do this processing step faster than SQL.

 

user11590

The Magic ETL idea worked perfectly!  I appreciate your help!