Opioids prescribed per Emergency Department visit

[Margaret]

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.)

[Digvijay_Singh · Accepted solution]

May be this -

[Margaret]

Right but HOW? 🙂

What expression do I use? Did you use??

[Digvijay_Singh]

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

[Margaret]

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]))

[Digvijay_Singh]

I think you can count IDs, I just tried to match numbers

[Margaret]

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)

[Digvijay_Singh]

Great!

[Margaret]

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?

[Digvijay_Singh]

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?