3 Replies Latest reply on Mar 3, 2017 5:43 PM by Vandana Samtani

# case statement calculation

Hi All,

I am trying to get a count of users that signed up by payment method. Since I want to combine couple of payment method into one, i am planning to do this using case statement.

For e.g.

Case when Media source like '%smarttv%' then smarttv'

when payment_method in (ABC,DEF) Then 'Bank of america'

When payment method in (GHI) then 'Chase'

When payment_method in (HIJ) the 'Wellsfargo'

end as payment_method is null then 'None'

Else payment source end

Any suggestions will be helpful on how to get counts using case statement or if there any other approaches. Below is the sample data -

Thanks,

Vandana

sample data

 Userid Payment Method sign up date Media source 1 ABC 2/1/2017 facebook 2 DEF 2/1/2017 appl 3 ABC 2/1/2017 smartv 4 GHI 2/1/2017 facebook 5 HIJ 2/1/2017 appl 6 ABC 2/2/2017 smartv 7 DEF 2/2/2017 facebook 8 ABC 2/2/2017 appl 9 GHI 2/2/2017 smartv 10 HIJ 2/2/2017 facebook 11 KLM 2/2/2017 appl 12 NOP 2/2/2017 smartv 12 ABC 2/3/2017 facebook 12 DEF 2/3/2017 appl 12 ABC 2/3/2017 smartv 12 GHI 2/3/2017 facebook 12 HIJ 2/3/2017 appl
• ###### 1. Re: case statement calculation

Hi Vandana,

Case statements are useful when you have individual cases, like:

CASE [Field]

WHEN 1 THEN ...

WHEN 2 THEN ...

WHEN 3 THEN ...

ELSE ...

END

But if it gets more complex than that (ranges of values, wildcard matching, etc...) then I would suggest an if statement.  Yours could be something like:

IF CONTAINS([Media source], "smarttv") THEN "smarttv"

ELSEIF payment_method = "ABC" OR payment_method = "DEF" Then 'Bank of america'

ELSEIF payment_method = "GHI" then 'Chase'

ELSEIF payment_method = "HIJ" the 'Wellsfargo'

ELSEIF ISNULL(payment_method) THEN 'None'

ELSE [payment source]

END

Hope that helps!

Joshua

1 of 1 people found this helpful
• ###### 2. Re: case statement calculation

Hi Joshua,

Vandana

1 of 1 people found this helpful
• ###### 3. Re: case statement calculation

Hi Joshua,

I added Counts With date conditions one more question.