on 03-22-2016 7:14 PM
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.
Hi,
An easy solution is to create a derived table in the universe.
The SQL will use2 sub-select statements:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.