Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Help to handle null in FirstSortedValue expression

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.

3 Replies
MVP
MVP

Re: Help to handle null in FirstSortedValue expression

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)

Not applicable

Re: Help to handle null in FirstSortedValue expression

Thanks Tresesco,

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

MVP
MVP

Re: Help to handle null in FirstSortedValue expression

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.