topic Urgently need some help. in QlikView

Urgently need some help.

Fri, 22 Nov 2013 03:07:59 GMT

Hello everyone, I would appreciate this if anyone could help me out with this question.

If I have hospitals as a field and three months of quantity preceding this column, i.e. 201307, 201308, 201309. I want to sort out the hospitals that have any two out of the three months >1 quantity. How can I do this? In Excel, this would have been countif(A2:A3, >1), followed by filter/sorting the table with >1. How can I achieve this in Qlikview?

Thanks,

Charles

Re: Urgently need some help.

jagan — Fri, 22 Nov 2013 03:56:16 GMT

Hi,

Please find attached file for solution hope it helps you.

Regards,

Jagan.

Re: Urgently need some help.

sujeetsingh — Fri, 22 Nov 2013 03:56:53 GMT

Hello see the sample i have pasted

Re: Urgently need some help.

Fri, 22 Nov 2013 04:47:34 GMT

Dear Charles Liu,

Please try this.

Thank you.

Re: Re: Urgently need some help.

Fri, 22 Nov 2013 05:30:27 GMT

Thank you all for your quick responses. But I might have phrased the question wrong that I wasn't getting the results I wanted.

Please see the table below first:

Hospital	201307	201308	201309
	Quantity	Quantity	Quantity
Christie Hospital	2	0	3
St. John's Hospital	1	2	2
Philly Hospital	0	0	2
Mount Sinai Hospital	1	3	1

Criteria
MET (>1 quantity in at least 2 out of 3 months)
UNMET

What I want to achieve is when selecting "MET" in the field "Criteria", the table above would only show qualifying hospitals (like table below):

Hospital	201307	201308	201309
	Quantity	Quantity	Quantity
Christie Hospital	2	0	3
St. John's Hospital	1	2	2

I hope this clarifies my original question and I hope this would not be too much of a challenge for you qlikview gurus.

Thanks

Charles

Re: Re: Re: Urgently need some help.

Fri, 22 Nov 2013 07:45:30 GMT

Hi,

Check this attach file and let me know.

I hope this helps you.

Regards,

Ashutosh

Re: Urgently need some help.

jagan — Fri, 22 Nov 2013 07:52:40 GMT

Hi,

Try this in script,

Data:

LOAD
Hospital, M201307, M201308, M201309
WHERE Flag = 1;
LOAD
*,
If(Rangesum(If(M201307 > 1, 1), If(M201308 > 1, 1), If(M201309 > 1, 1)) >=2, 1) AS Flag
INLINE [
Hospital, M201307, M201308, M201309
Christie Hospital, 2, 0, 3
St. John's Hospital, 1, 2, 2
Philly Hospital, 0, 0, 2
Mount Sinai Hospital, 1, 3, 1];

Regards,

Jagan.

Re: Urgently need some help.

Fri, 22 Nov 2013 08:59:35 GMT

hi ,

use the below expression in pivot .. it works for me which returns 2 hospital as per ur result.

if(aggr(Nodistinct sum(if(quantity>=2,1,0)),Hospital)>=2,sum(Quantity))

Regards

kumar.R

Re: Re: Re: Urgently need some help.

Fri, 22 Nov 2013 09:21:52 GMT

Hello Charles,

Please find the attached document and the excel file. I have also added the filter.

Thanks,

Angad