topic Re: Multiple alternate states expression/comparison question in QlikView

Multiple alternate states expression/comparison question

phcaptjim — Mon, 20 Mar 2017 14:10:02 GMT

I have a view where I have 7 different alternate states. The user can find a population of patients in each 'state' then I have a chart that compares the 7 states and shows how many states each patient falls into.

Here are the column headers:

Each column (C1, C2, etc) has an expression like this:

=Sum({C1}[Patient Counter])

There will be a 1 if the patient is in that state or a zero if not.

Then I have a 'Sum' column that reports how many states each patient falls into:

=C1+C2+C3+C4+C5+C6+C7

My final column is a link to the chart in our electronic health record. Right now I am getting some blank values at random points and I can't figure out why. Here is the expression I am using for that column:

=If(C7=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C6=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C5=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C4=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C3=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C2=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink],

If(C1=1,[Patient MRN]&'<url>'&[EPIC Patient Hyperlink]

)))))))

Since I have to put this hyperlink as an expression (I wish we could do dimensions as expressions) I have to use an expression so I don't get every patient. This chart is NOT in an alternate state since I need it to ready all the states. Everything works well except that last column. Does anyone have an neat ideas on this?

Thanks!

Re: Multiple alternate states expression/comparison question

sunny_talwar — Mon, 20 Mar 2017 15:06:29 GMT

May be like this

=If(C7=1,Only({C7}[Patient MRN])&'<url>'&[EPIC Patient Hyperlink],

If(C6=1,Only({C6}[Patient MRN])&'<url>'&[EPIC Patient Hyperlink],

If(C5=1,Only({C5}[Patient MRN])&'<url>'&[EPIC Patient Hyperlink],

If(C4=1,Only({C4}[Patient MRN])&'<url>'&[EPIC Patient Hyperlink],

If(C3=1,Only({C3}[Patient MRN])&'<url>'&[EPIC Patient Hyperlink],

If(C2=1,Only({C2}[Patient MRN])&'<url>'&[EPIC Patient Hyperlink],

If(C1=1,Only({C1}[Patient MRN])&'<url>'&[EPIC Patient Hyperlink]

)))))))

Re: Multiple alternate states expression/comparison question

phcaptjim — Mon, 20 Mar 2017 15:31:49 GMT

Thanks Sunny!

I had to apply the ONLY function to both sides of the expression and now it's working properly. YAHOO!!

=If(C7=1,Only({C7}[Patient MRN])&'<url>'&Only({C3}[EPIC Patient Hyperlink]),

If(C6=1,Only({C6}[Patient MRN])&'<url>'&Only({C3}[EPIC Patient Hyperlink]),

If(C5=1,Only({C5}[Patient MRN])&'<url>'&Only({C3}[EPIC Patient Hyperlink]),

If(C4=1,Only({C4}[Patient MRN])&'<url>'&Only({C3}[EPIC Patient Hyperlink]),

If(C3=1,Only({C3}[Patient MRN])&'<url>'&Only({C3}[EPIC Patient Hyperlink]),

If(C2=1,Only({C2}[Patient MRN])&'<url>'&Only({C2}[EPIC Patient Hyperlink]),

If(C1=1,Only({C1}[Patient MRN])&'<url>'&Only({C1}[EPIC Patient Hyperlink])

)))))))

Re: Multiple alternate states expression/comparison question

sunny_talwar — Mon, 20 Mar 2017 15:40:10 GMT

Awesome

Re: Multiple alternate states expression/comparison question

phcaptjim — Mon, 20 Mar 2017 16:37:32 GMT

One additional wrinkle to this question, how do I omit any selections from the stateless portion of the app when this expression? If only certainly patients are selected in the main, only those links will appear in my chart so I need to omit all selections in this expression.

Re: Multiple alternate states expression/comparison question

sunny_talwar — Mon, 20 Mar 2017 16:39:46 GMT

It seems like everything has a state... what is stateless here?

Re: Multiple alternate states expression/comparison question

phcaptjim — Mon, 20 Mar 2017 16:43:51 GMT

The patient MRN and patient hyperlink are actually coming from the stateless portion of the application. Thus selections that are applied to each state don't apply to those two fields. I'm probably doing a great job of explaining this but each population (C1, C2, etc) has different selections applied to it but those two values that we are using above are not filtered based on any of those states. Does that help?

Re: Multiple alternate states expression/comparison question

sunny_talwar — Mon, 20 Mar 2017 16:47:40 GMT

Not entirely sure I understand... I am not sure what you are getting right now and what do you expect to get.... May be if you can show what you are getting through screenshots and explain what is needed, I might be able to help you

Re: Multiple alternate states expression/comparison question

phcaptjim — Mon, 20 Mar 2017 16:52:22 GMT

In my alternate state comparison chart I have 3 patients (MRN 1111, 2222, 3333)

In the main application lets say we have two patients selected, MRN 1111, 2222

So my comparison chart will look something like this:

MRN C1 C2 C3 Summary

1111 1 0 0 1

2222 1 0 0 1

1 0 0 1

The MRN value for the third patient is not showing since it's not selected in the main portion of the application. If I unselect all from the main app, I would get this:

MRN C1 C2 C3 Summary

1111 1 0 0 1

2222 1 0 0 1

3333 1 0 0 1

Hopefully that helps.

Re: Multiple alternate states expression/comparison question

sunny_talwar — Mon, 20 Mar 2017 16:57:44 GMT

You are still seeing 1s and 0s, but not the MRN itself

Re: Multiple alternate states expression/comparison question

phcaptjim — Mon, 20 Mar 2017 17:06:38 GMT

Yes. The reason is the 1's and 0's are from the alternate states where the other field is not.

Re: Multiple alternate states expression/comparison question

sunny_talwar — Mon, 20 Mar 2017 17:09:47 GMT

Why don't you make MRN from alternate state also?

Re: Multiple alternate states expression/comparison question

phcaptjim — Mon, 20 Mar 2017 17:21:47 GMT

I'm not sure how that would work. Each state (C1, C2, etc) will have different lists of MRN's but the chart is a master list of all MRN's from the states. For example....

MRN

111 --comes from C1

222 --comes from C2

333 --comes from C3

444 --comes from C3 and C4

Also, I just realized the original solution doesn't always work.

Notice some hyperlinks are underlined and some are not. I didn't realize this would be so challenging.

NOTE: These values have been scrambled and do not contain real data.

Re: Multiple alternate states expression/comparison question

sunny_talwar — Mon, 20 Mar 2017 17:34:48 GMT

May be this

Only({C1+C2+C3+C4+C5+C6+C7}[Patient MRN])

Re: Multiple alternate states expression/comparison question

bvssudhakar — Wed, 13 Jun 2018 10:51:56 GMT

Hi Sunny,

Here, I am having also one Query like this

I have 6 alternate states named as set1, set2, set3, set4, set5, set6 (These 6 are Current Selection boxes i.e i used state name function) and i have 4 list boxes named as year, Category, Product name, Customer (Now these 4 are with another alternate state named as 'proposed').

Now i have created one straight table with these columns year, Category, Product name, Customer, Sum(Quantity).

But, i want to show the values when we pass our selections to that sets those values only i want to see.

for that i used sum({Set1 + Set2 + Set3 + Set4 + Set5 + Set6} Quantity). Here, also i am getting problem.. when i pass the selections to all sets that time only it is giving correct result.

My requirement is if i pass my selections to only set1 then also i wnat correct result / if pass the selections to 2 or 3 sets then that time also i want correct result

For that How can i write my expression can you give me the suggestion apart from i used these below expressions also:

=sum({<[Sales Person]=Set1 ::[Sales Person],Customer=Set1 ::Customer>} Quantity)

+sum({<[Sales Person]=Set2 ::[Sales Person],Customer=Set2 ::Customer>} Quantity)

+sum({<[Sales Person]=Set3 ::[Sales Person],Customer=Set3 ::Customer>} Quantity)

+sum({<[Sales Person]=Set4 ::[Sales Person],Customer=Set4 ::Customer>} Quantity)

+sum({<[Sales Person]=Set5 ::[Sales Person],Customer=Set5 ::Customer>} Quantity)

=sum({<[Sales Person]=Set1 ::[Sales Person],Customer=Set1 ::Customer>} Quantity)

&sum({<[Sales Person]=Set2 ::[Sales Person],Customer=Set2 ::Customer>} Quantity)

&sum({<[Sales Person]=Set3 ::[Sales Person],Customer=Set3 ::Customer>} Quantity)

&sum({<[Sales Person]=Set4 ::[Sales Person],Customer=Set4 ::Customer>} Quantity)

&sum({<[Sales Person]=Set5 ::[Sales Person],Customer=Set5 ::Customer>} Quantity)

=num(sum({(vFilterStates)} Quantity)) ---------------(vFilterStates is the variable which i created)

(This is not giving any result)

=Sum({(vFilterStates)} Quantity)

(This is also not giving any result)

For "vFilterStates" Variable i used this expression

= If( len(GetCurrentSelections('','',1,'Set1'))

+ len(GetCurrentSelections('','',1,'Set2'))

+ len(GetCurrentSelections('','',1,'Set3'))

+ len(GetCurrentSelections('','',1,'Set4'))

+ len(GetCurrentSelections('','',1,'Set5'))

+ len(GetCurrentSelections('','',1,'Set6'))

=0, '$',

if(len(GetCurrentSelections('','',1,'Set1'))>0, 'Set1+','')

& if(len(GetCurrentSelections('','',1,'Set2'))>0, 'Set2+','')

& if(len(GetCurrentSelections('','',1,'Set3'))>0, 'Set3+','')

& if(len(GetCurrentSelections('','',1,'Set4'))>0, 'Set4+','')

& if(len(GetCurrentSelections('','',1,'Set5'))>0, 'Set5+','')

& if(len(GetCurrentSelections('','',1,'Set6'))>0, 'Set6+',''))

Can you please help me out from this

Re: Multiple alternate states expression/comparison question

sunny_talwar — Wed, 13 Jun 2018 10:58:26 GMT

Would you be able to share a qvw sample to show your issue?