When x is null and y and z is not null then case statement - multiple parameters

leeloo_dallas

Hey there I'm trying to perform a case statement in magic etl using the formula widget.

I'm trying to use automl tool to predict the likelihood of receiving a like, comment, and/or shares based on a section topic. 

 

I'm not sure if I'm on the right path though. 

 

(CASE
WHEN `likes` IS NOT NULL AND `comments` OR `shares` IS NULL THEN "likes"

WHEN `comments` IS NOT NULL AND `likes` OR `shares` IS NULL THEN "comments"

WHEN `shares` IS NOT NULL AND `comments` OR `likes` IS NULL THEN "shares"

WHEN `likes` IS NULL AND `comments` OR `shares` IS NOT NULL THEN "likes, shares"

WHEN `shares` IS NULL AND `likes` OR `comments` IS NOT NULL THEN "likes, comments"

WHEN `comments` IS NULL OR `likes` OR `shares` IS NOT NULL THEN "comments, shares"

WHEN `comments` AND `likes` AND `shares` IS NOT NULL THEN "likes,comments,shares"

WHEN `comments` AND `likes` AND `shares` IS NULL THEN "none"

ELSE 0
END
)

leeloo_dallas

(CASE
WHEN `likes` IS NOT NULL AND `comments` IS NULL AND `shares` IS NULL THEN likes
WHEN `comments` IS NOT NULL AND `likes` IS NULL AND `shares` IS NULL THEN comments
WHEN `shares` IS NOT NULL AND `comments` IS NULL AND `likes` IS NULL THEN shares
WHEN `comments` IS NOT NULL AND `likes` IS NOT NULL AND `shares` IS NULL THEN comments/likes
WHEN `comments` IS NOT NULL AND `shares` IS NOT NULL AND `likes` IS NULL THEN comments/shares
WHEN `shares` IS NOT NULL AND `likes` IS NOT NULL AND `comments` IS NULL THEN shares/likes
ELSE 0
END
)

leeloo_dallas

(CASE
WHEN `likes` IS NOT NULL AND `comments` IS NULL AND `shares` IS NULL THEN likes
WHEN `comments` IS NOT NULL AND `likes` IS NULL AND `shares` IS NULL THEN comments
WHEN `shares` IS NOT NULL AND `comments` IS NULL AND `likes` IS NULL THEN shares
WHEN `comments` IS NOT NULL AND `likes` IS NOT NULL AND `shares` IS NULL THEN comments/likes
WHEN `comments` IS NOT NULL AND `shares` IS NOT NULL AND `likes` IS NULL THEN comments/shares
WHEN `shares` IS NOT NULL AND `likes` IS NOT NULL AND `comments` IS NULL THEN shares/likes
ELSE 0
END
)

jaeW_at_Onyx

ummm... it might be important to define what you want more specifically... Auto ML can only take you so far right? 

 

THink about what it would take if you had to do this by hand ... what would it look like?  Let's break your problem down into plain Enlgish. 

 

Let's say we were looking at brands of makeup and i want to know, if i mention x brand, will it get likes, shares and comments?  This is CLASSIFICATION (a google-able term) and ideally, the answer would be a 1 or a 0 with an indicator of how confident i am in my guess.

 

If i made you do this by hand, you'd take all the things that mention Sephora, you'd compare how often it got a like against all the rows.  If you got 55 likes / 100 rows, you'd assume, most of the time if i mention Sephora i'll get a like.

 

Conversely, if you had 30 likes out of 100 rows regarding Hot Topic, you'd assume, "If i mention hot topic i probably won't get a like (7 out of 10 times)

 

This question of "will i get a like" is completely separate from the question of "will i get a share or a comment."  UNLESS your question is "If you are Sephora AND you got a Like will you get a Share"

 

With that in mind, i guess what i'm getting to is, even though your code works, you probably don't want to collapse your metric into one column using a CASE statement.