topic Re: Missing records in FirstSortedValue? in QlikView

Missing records in FirstSortedValue?

baylor2016 — Tue, 01 Aug 2017 12:30:44 GMT

Hi,

I am trying to get all the latest blood pressure value for each patient by using the following code, it returns very small number (480) instead of 2175.

=count(aggr(

FirstSortedValue(

distinct

YearMonth,

FLAG_HPN={1},

CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},

PAT_ID = {"=Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) >= 18 and

Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) <= 59"},

VISITTYPE={'Office Visit'},

FCMVISIT={1},

BP_SYSTOLIC={"*"}

BP_SYSTOLIC ,-CONTACT_DATE2,PAT_ID)

,PAT_ID)

)

The code below returns 2175

=count(distinct{<

YearMonth,

FLAG_HPN={1},

CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},

PAT_ID = {"=Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) >= 18 and

Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) <= 59"},

VISITTYPE={'Office Visit'},

FCMVISIT={1},

BP_SYSTOLIC={"*"}

>} PAT_ID

)

Not sure what the problem is.

Thanks

Longmatch

Re: Missing records in FirstSortedValue?

Anil_Babu_Samineni — Tue, 01 Aug 2017 12:36:15 GMT

Try this for latest blood pressure. Just remove DISTINCT and try

=count(aggr(FirstSortedValue({<YearMonth, FLAG_HPN={1},

CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},

PAT_ID = {"=Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) >= 18 and

Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) <= 59"},

VISITTYPE={'Office Visit'}, FCMVISIT={1}, BP_SYSTOLIC={"*"}>} BP_SYSTOLIC ,-CONTACT_DATE2,PAT_ID),PAT_ID))

Re: Missing records in FirstSortedValue?

baylor2016 — Tue, 01 Aug 2017 12:42:27 GMT

With DISTINCT returns 77, it returns 480 without Distinct. The correct number should be 2175.

Re: Missing records in FirstSortedValue?

sunny_talwar — Tue, 01 Aug 2017 12:47:08 GMT

If you are getting the count you want with one of the two expression, why are you even looking for an alternative? Also, just looking at complex expression don't really help, may be provide a sample to look at the issue

Re: Missing records in FirstSortedValue?

Anil_Babu_Samineni — Tue, 01 Aug 2017 12:47:46 GMT

Not sure, If second expression returns correct one then what is the use of first expression?

Re: Missing records in FirstSortedValue?

baylor2016 — Tue, 01 Aug 2017 12:50:53 GMT

I need to get the number of patients who have high blood pressure (>140/90), I used the another one as reference making sure the number returned correct. Thanks. I will prepare a sample for this. Thanks

Re: Missing records in FirstSortedValue?

baylor2016 — Tue, 01 Aug 2017 14:44:05 GMT

My problem was solved by added the set analysis string to the count level. Is there any to make the script shorter, or can I save the definition of set analysis to a variable? Thanks

=count( {<

YearMonth,

FLAG_HPN={1},

CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},

PAT_ID = {"=Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) >= 18 and

Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) <= 59"},

VISITTYPE={'Office Visit'},

FCMVISIT={1},

BP_SYSTOLIC={"*"}

aggr(

FirstSortedValue(

YearMonth,

FLAG_HPN={1},

CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},

PAT_ID = {"=Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) >= 18 and

Age(Num(V_selectedDate), Only({<CONTACT_DATE2 = {""$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))""}, YearMonth>}BIRTH_DATE)) <= 59"},

VISITTYPE={'Office Visit'},

FCMVISIT={1},

BP_SYSTOLIC={"*"}

BP_SYSTOLIC ,-CONTACT_DATE2)

,PAT_ID)

)

Re: Missing records in FirstSortedValue?

sunny_talwar — Tue, 01 Aug 2017 14:46:44 GMT

You can def. save the set analysis to the variable.... the only problem you might run into is that the expression editor not understanding your syntax because of the use of variable... but it should work seamlessly.

Re: Missing records in FirstSortedValue?

baylor2016 — Tue, 01 Aug 2017 14:51:25 GMT

Thanks. It works perfectly!