Creating a new category field using existing fields within the data set

user04300
user04300 Member
in Magic ETL

Hi,

I would like to create a new field to categorize our different insurance payers into a few main buckets.  The logic involves using three exisiting fields to determine which payer bucket each row (insurance claim) should be called.  Is it best to do this as magic transform or do in a beast mode?

 

Currently I am using a nested If statement in Excel to accomplish this: 

IF(`Ins Product`="MCR Replcmt","MCR Replcmt",

IF(OR(`Financial Class`="Medicare",`Financial Class`="Medicaid",`Financial Class`="Medicaid Replacement"),"Medicare",

IF(Plan Grp="AETN","AETNA",

IF(Plan Grp="BCTX","BCBS",

IF(Plan Grp="CIGNA","CIGNA",

IF(Plan Grp="UHC","UHC",

"OTHER"))))))

 

I am new to Domo, so I am not yet familar with the syntax in beast mode.  If this is the best method, a formula would be most useful and much appreciated!

 

Thank you!

Best Answers

  • user04300
    user04300 Member
    Answer ✓

    Yes, basically, the logic should look for all MCR Replacement products first and name them as such regardless of the payer (Aetna, UHC, etc).  Second, the logic should look for Medicare payers and call them as such, and finally it should look for the major payers listed in my formula and call them as such and the remaining list of payers should be all grouped into an "Other" bucket.  We have about a hundred different payers, but we only need to call out the biggest payers and put the rest into on bucket.

     

  • user04300
    user04300 Member
    Answer ✓

    Ah, yes that did it.  It works now.  Thanks for your help.

Answers

  • AndreaHenderson
    AndreaHenderson Domo Product Manager

    Hi!  This sounds like a great case for a Beast Mode, and I'd be happy to write that for you.  Can you please reply with your business logic in conversational English?  I think I have it figured out from the Excel formula, but I want to be sure I have the correct logic before I write the Beast Mode.  Thanks!

    Domo Product Manager for Data Transformation (MagicETL)

  • AndreaHenderson
    AndreaHenderson Domo Product Manager

    For reference, here is my first pass at the Beast Mode:

     

    (CASE WHEN `Ins Product` = 'MCR Replcmt' THEN 'MCR Replcmt'
    WHEN `Financial Class` IN ('Medicare', 'Medicaid', 'Medicaid Replacement') THEN 'Medicare'
    WHEN `Plan Grp` = 'AETN' THEN 'AETNA'
    WHEN `Plan Grp` = 'BCTX' THEN 'BCBS'
    WHEN `Plan Grp` = 'CIGNA' THEN 'CIGNA'
    WHEN `Plan Grp` = 'UHC' THEN 'UHC'
    ELSE 'OTHER' END)

     

    In this statement, the CASE statement will evaluate each "WHEN" sequentially, and will stop evaluating after the first true statement it encounters.  If your business criteria requires that multiple scenarios be taken into account in conjunction with each other, we can write it that way too.

     

    /* Edited to correct syntax */

    Domo Product Manager for Data Transformation (MagicETL)

  • user04300
    user04300 Member
    Answer ✓

    Yes, basically, the logic should look for all MCR Replacement products first and name them as such regardless of the payer (Aetna, UHC, etc).  Second, the logic should look for Medicare payers and call them as such, and finally it should look for the major payers listed in my formula and call them as such and the remaining list of payers should be all grouped into an "Other" bucket.  We have about a hundred different payers, but we only need to call out the biggest payers and put the rest into on bucket.

     

  • user04300
    user04300 Member

    I think this case statement is exactly what I am looking for.   Thank you!

  • AndreaHenderson
    AndreaHenderson Domo Product Manager

    Excellent, thanks for the clarification.  I think my first pass at the Beast Mode should work for you, then.  Click the "Add Calculated Field" button in the Analyzer interface, plug in the CASE statement, give the calculation a name and save.  Let me know if you have further questions.  Here is more info about Beast Mode calculations:  http://knowledge.domo.com?cid=beastmode 

    Domo Product Manager for Data Transformation (MagicETL)

  • user04300
    user04300 Member

    I am getting a syntax error when I input the case statement.  Is there a misplaced ( or ` ?

  • AndreaHenderson
    AndreaHenderson Domo Product Manager

    Sorry about that, I didn't have a way to test because I'm not in your instance of Domo.  There is an erroneous comma after the first instance of 'UHC'.  If you remove that, I think it will work.

    Domo Product Manager for Data Transformation (MagicETL)

  • user04300
    user04300 Member
    Answer ✓

    Ah, yes that did it.  It works now.  Thanks for your help.

Categories