Case Statement in Magic ETL

Reply
Yellow Belt

Case Statement in Magic ETL

Hello,

 

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

 

Thank you,

Nick

Moderator

Re: Case Statement in Magic ETL

Could someone help with this request?

Major Brown Belt

Re: Case Statement in Magic ETL

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!

 

 

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Welcome to Dojo

Re: Case Statement in Magic ETL

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!

Purple Belt

Re: Case Statement in Magic ETL

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

Visitor

Re: Case Statement in Magic ETL

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?

Brown Belt

Re: Case Statement in Magic ETL

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.

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!