Multiple Case Statements Wildcard matches

Help
, ,
1

Hello!

I’m trying to create a set something similar to the following

{%- set payment_methods = ['bank_transfer', 'credit_card', 'coupon', 'gift_card'] -%}

with payments as (
   select * from {{ ref('stg_payments') }}
),

final as (
   select
       order_id,
       {% for payment_method in payment_methods -%}

       sum(case when payment_method = '{{ payment_method }}' then amount else 0 end)
            as {{ payment_method }}_amount

       {%- if not loop.last -%}
         ,
       {% endif -%}

       {%- endfor %}
   from {{ ref('stg_payments') }}
   group by 1
)

select * from final

However, instead of setting the payment methods to be exact matches, I would like to use Wildcard matches.
e.g.

CASE WHEN payment_method LIKE %Amex% THEN "Credit Card" 
        ELSE 
           WHEN payment_method LIKE %Paynow% THEN "Bank Transfer" ELSE "Others" END

My current SQL in the dbt model has many lines of case statements and within each case statements many different variations of wildcard matches.

Appreciate some help on improving upon this and making the model cleaner with lesser lines of code and also making it easier to add on more variations of wildcard matches.

Thanks!

2

Hey @derrickt! If I’m understanding you correctly, you have have a lot of different types of credit card (Amex, Visa, Mastercard, etc), and lots of different types of bank transfers (Paynow etc), and want to flatten them down to just credit card, bank transfer, etc?

If so, instead of a case statement you could consider a seed, a csv file that gets turned into a table in your warehouse.

If you made something like this:
A table with two columns: payment type and payment group

Then you could do a join against the seed:

with payment_map as (
  select * from {{ ref('payment_map') }} --use the name of your seed file in the ref
),

payments as (
  select * from {{ ref('payments') }}
),

final as (
select 
  payments.*,  
  coalesce(payment_map.payment_group, "Unknown") as payment_group
from payments
  left outer join payment_map 
    on payments.payment_method ilike '%' + payment_type + '%'
)

For more info on why this works, check out mysql - how to use a like with a join in sql? - Stack Overflow

Also keep in mind that if you have something like Amex and Amex Black in your seed file, then you will get a fanout because Amex Black will match against both rows. Remember to add a uniqueness test on your finished model!

3

Hey @joellabes

Thank you for the suggestion!
Appreciate it!

Yeah I think in this case since I have alot of different types of Payment methods and I would want to group them to be under 3 Categories: Credit Card, Bank Transfer and Online Payments.

A Seed file would be the most appropriate solution for this.

Will try it out and see if it would work!

1 Like