Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to calculate the rate at which various providers prescribe controlled substances:
For each provider: Count(Rx) / Count(Visits)
Sometimes a provider prescribes a medication to a patient they aren't assigned to.
Sometimes more than one provider is assigned to a single patient.
Here are the two tables I have:
Account Number | Rx Provider | Medication | Rx ID |
12667 | Brown, PA | HYDROmorphone HCL | 15213627357 |
12667 | Brown, PA | HYDROmorphone HCL | 15233728439 |
12667 | Dr. McDonald | fentaNYL | 15182933689-2 |
12667 | Dr. McDonald | HYDROCO37|Hydrocodone | 15194001371 |
12667 | Dr. McDonald | HYDROCOD/APAP 5/325 | 15193742325 |
12667 | Dr. McDonald | HYDROmorphone HCL | 15182933689 |
27025 | Dr. Rodriguez | chlordiazePOXIDE 25MG | 29011739391 |
27025 | Dr. Wong | LORazepam | 28222549500-2 |
27025 | Dr. Wilson | LORazepam | 28232607599 |
Account Number | Visit Provider |
12667 | Brown, PA |
27025 | Dr. Rodriguez |
27025 | Dr Wong |
27025 | Dr Wilson |
I would like to create an object for the end user that looks like this:
Provider | Rx's | Visits | Rx's/Visit |
Brown, PA | 2 | 1 | 2/1 |
Dr. McDonald | 4 | 0 | 4/0 |
Dr. Rodriguez | 1 | 1 | 1/1 |
Dr. Wong | 1 | 1 | 1/1 |
Dr. Wilson | 1 | 1 | 1/1 |
(I also need to make a line graph showing Providers Rx/Visit rate over time with MonthYear as the X axis--so if I end up having to count providers visits in the script, somehow I would have to relate it back to my Calendar table.)
May be this -
May be this -
Right but HOW? 🙂
What expression do I use? Did you use??
I created island provider table to have all names at one place and can be used as dimension, here the script - (I found one other problem, you need to clean your names, Somewhere it has 'Dr.' and somewhere without dot after 'Dr'
Rx:
LOAD [Account Number],
replace(trim([Rx Provider]),'.','') as [Rx Provider],
Medication,
[Rx ID]
FROM
[https://community.qlik.com/thread/294627]
(html, codepage is 1252, embedded labels, table is @1);
visit:
LOAD [Account Number],
replace(trim([Visit Provider]),'.','') as [Visit Provider]
FROM
[https://community.qlik.com/thread/294627]
(html, codepage is 1252, embedded labels, table is @2);
Provider:
Load
distinct [Rx Provider] as Provider
Resident Rx;
Concatenate
Load distinct
[Visit Provider] as Provider
Resident visit
Where Not Exists(Provider,[Visit Provider])
The expression used are -
For Rx - Count( If(Provider=[Rx Provider],[Rx Provider]))
For visits - Count( If(Provider=[Visit Provider],[Visit Provider]))
For Rx/Visit - Column(1)/Column(2)
I attached qvw as well in the last post. Thanks
Thank you. I definitely didn't see a .qvw on the first post, but I see it now.
Shouldn't I be counting ID's like this, or is it the same thing since you didn't use DISTINCT?
Count( If(Provider=[Rx Provider],[Rx ID]))
Count( If(Provider=[Visit Provider],[Account Number]))
I think you can count IDs, I just tried to match numbers
I think it's working, thank you!!
I also changed it to Set Analysis and then I don't have to wait for the chart to load:
count({<Provider={"=$([Visit Provider])"}>}DISTINCT VisitID)
Great!
Except using Set Analysis totals the count for everyone and assigns it to each provider instead of separate for each provider.
count({<Provider={"=$([Visit Provider])"}>}DISTINCT [Account Number])
Is there a way to use set analysis without it doing that?
I think set analysis won't work here because Provider is an island field and not associated to rest of the data model. Do you see too much slowness with the if?