Case When Where Object in Universe

[Former Member]

We have a reporting requirement where we need to display for each client the Medicaid policy ID with the Managed Care plan name.  These are 2 different insurances with 2 different plan names and ID's.  All clients that have a Managed Care Insurance policy will also have Medicaid. 

The problem is when I pull clients into a webi report they will have multiple lines, one for each insurance.

Client Name	Policy ID	Insurance Name
Joe Smith	1234567	Medicaid
Joe Smith	7654321	Managed Care

We only want to see one line per client with Medicaid ID with Managed Care Name

Client Name	Policy ID	Insurance Name
Joe Smith	1234567	Managed Care

I haven't been successful in solving this in the desktop Webi application so I was thinking I could create an object in the Universe.

I tried...

CASE

WHEN Insurance ID in("managed care insurance id's") THEN  Policy ID

WHERE (Insurance ID = "Medicaid insurance ID"))

ELSE Policy ID

END

I know after some research that the where clause needs to be before the case when, but I can't figure out how to write it. I'm not sure this is even possible.

Any help would be much appreciated.

[Former Member · Accepted solution]

Hi,

An easy solution is to create a derived table in the universe.
The SQL will use2 sub-select statements:

• 1 statement for Medicaid
• 1 statement for Managed Care

SELECT "Client Name", A."Managed Care", B."Medicaid" FROM

(SELECT "Client Name", "Policy ID" as "Medicaid" FROM .... WHERE "Insurance Name" = 'Medicaid') A,

(SELECT "Client Name", "Policy ID" as "Managed Care" FROM .... WHERE "Insurance Name" = 'Managed Care') B

So you will have only 1 record for the customer including "Managed Care" and "Medicaid".

Regards,

Didier

[Former Member · Accepted solution]

Thank you. I think that a derived table will be my best bet. I will take what you gave me and test it out.

[Former Member]

What about if you create 2 objects - Policy ID (Managed Care) and Policy ID (Medicaid):

something like:

CASE WHEN Insurance ID in("managed care insurance id's") THEN  Policy ID END

CASE WHEN Insurance ID in("medicaid insurance id's") THEN  Policy ID END

Then create a 3rd object to achieve what you want and get one ID.

You would perform the test on the new objects you created above:

CASE WHEN Policy ID (Managed Care) is not null then Policy ID (Managed Care) else Policy ID (Medicaid)