Case Statement in Magic ETL

Nick_Bertz

Hello,

 

Is there a way to implement a Case Statement (not nested Case Statements) in Magic ETL?

 

Thank you,

Nick

KaLin

Could someone help with this request?

PodiumMason

Hi Nick!

 

There is a transform options in Magic that allow you to do case statement functionalities. 

 

Under the "Edit Data" transform grouping you have the "Value Mapper" transform. This transform allows you to (much like a case statement) look at a specific column for a value, empty or null string, or text string, then based on whether that is found or not, either replace the value in the given column, or create a new column with the new value. 

 

This is one example of an easy to use transform that can do some case statement logic. Often using this transform and other transforms, you should be able to duplicate most case statement logic. Sometimes a single transform will work, sometimes it won't. 

 

Let me know if you have a specific example you'd like to look at. Hope this is helpful!

 

 

user02062

Hi! I'd like to do a case statement that works on numbers (spend) and not on text. Can you help me out with that? For example:

 

if Site is A then pull Spend A

if Site is B then pull Spend B

end as Final Spend.

 

Thank you!

rado98

Filter your data set for Site A, Copy or rename Spend A as Final Spend

Filter your data set for Site B, Copy or rename Spend B as Final Spend

Append

user06381

And if not Site A or Site B, the ELSE part of a case statement?

 

So that you do not have rows disappearing from your data?

Unknown

Hi, @user06381,

 

For the "ELSE" portion, you'll need yet another Filter tile. In this tile, configure it such that none of the conditions referenced in the other Filters tiles are true. So, in this Filter tile, you'd have Site not equal to "Site A" and Site not equal to "Site B". This way, the first filter returns Site A; the second returns Site B; and the third returns all others where NOT Site A and NOT Site B.