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

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!

Find more posts tagged with

Accepted answers

user04300

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

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

All comments

AndreaHenderson

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!

AndreaHenderson

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 */

user04300

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

AndreaHenderson

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

user04300

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

AndreaHenderson

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.

user04300

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