Magic ETL Conditional Column

remember_Sagan
remember_Sagan Member
in Magic ETL

Hello,

 

I am brand new to Domo and looking for the equivalent data transformation in Power BI that is called "Add Conditional Column" which basically allows you to write a case statement based on values in existing columns in your data set. Ex. If [Sales Rep] = "Dan" and [Order Type] = "1" then "Exclude" else "Include". 

 

Is there a way to achieve this without filtering, adding a constant, then appending that back to the data set? (this was how a co-worker instructed me)

 

Or would this be a situation where just writing it in SQL would be better?

 

Thank you!

 

RS

Best Answer

  • NewsomSolutions
    NewsomSolutions Contributor
    Answer ✓

    @remember_Sagan Your co-worker was right on the filter/constant for Magic ETL.  But being better than SQL is hard to say.  I say that because yes it may be more complex to set up this situation in MagicETL, but the performance of the execution of this ETL will most likely always be much better than whatever you did in SQL.  This may not apply so you'd have to decide that, but if you went from 1k rows of data to 1M rows of data in SQL, you're most likely going to tell a difference in the execution of that SQL statement, but you would probably never notice it in Magic ETL.  

     

    Hope that helps.

    Matt

     

Answers

  • NewsomSolutions
    NewsomSolutions Contributor
    Answer ✓

    @remember_Sagan Your co-worker was right on the filter/constant for Magic ETL.  But being better than SQL is hard to say.  I say that because yes it may be more complex to set up this situation in MagicETL, but the performance of the execution of this ETL will most likely always be much better than whatever you did in SQL.  This may not apply so you'd have to decide that, but if you went from 1k rows of data to 1M rows of data in SQL, you're most likely going to tell a difference in the execution of that SQL statement, but you would probably never notice it in Magic ETL.  

     

    Hope that helps.

    Matt

     

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @remember_Sagan 

    I am more comfortable using MySQL, so I agree with your statement that I think this is easier using a MySQL dataflow

    SELECT
    CASE WHEN `Sales Rep`='Dan' AND `Order Type`='1' THEN 'Exclude' ELSE 'Include' END AS `Conditional Column`

    However, if the dataflow is already built in ETL, then I would recommend just using three steps mentioned.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories