topic Re: Help to handle null in FirstSortedValue expression in QlikView

Help to handle null in FirstSortedValue expression

Tue, 09 Jun 2015 06:46:01 GMT

I need a help to get highest(more recent) value from the dataset having null values.

Suppose, below is my inline table :

LOAD * INLINE [

ss_patient_id, EncounterDate, Group

1,6/5/2015, A

1,6/3/2015,B

1,5/19/2015,B

1,1/1/2015,C

2,6/5/2015, A

2,6/3/2015,B

2,3/13/2015,C

2,1/1/2015,B

3,6/5/2015, B

3,5/3/2015,B

3,4/13/2015,B

3,3/1/2015,A

3,2/13/2015,A

3,1/1/2015,A

1,6/5/2015,-

1,6/3/2015,-

1,5/19/2015,-

1,1/1/2015,-

];

Expected output is need to show recent or latest Group for every time period (Year, Quarter, Month).

Example: For patient id '1' above, for quarter 1, 'C' group should be displayed and for Quarter 2, 'A' should be displayed in 'Bar Chart.

Have used below expression in calculated dimension for Bar chart:

= aggr( nodistinct FirstSortedValue(Group,-EncounterDate & ss_patient_id),[New Group],ss_patient_id)

But this not seems to be working if we have null values in 'Group' column.

Need to handle null values in above expression. Find attached sample for more details.

Re: Help to handle null in FirstSortedValue expression

tresB — Tue, 09 Jun 2015 06:58:10 GMT

Those are not really null in your sample app but '-'. May be you can try like:

aggr( nodistinct FirstSortedValue({<Group-={'-'}>}Group,-EncounterDate & ss_patient_id),[New Group],ss_patient_id)

Re: Help to handle null in FirstSortedValue expression

Wed, 10 Jun 2015 11:28:26 GMT

Thanks Tresesco,

We can also avoid null values using "{<Group = {"*"}>}" in above expression. Is that right?

Re: Help to handle null in FirstSortedValue expression

tresB — Thu, 11 Jun 2015 04:31:32 GMT

We can. However, as I mentioned ealier, yours in sample are not really nulls. They are hyphen character, so they would be included in '*' set analysis which you probably don't want.