Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dgreenberg
Contributor III

Problem using FirstSortedValue on $Field =FirstSortedValue($Field,$FieldNo,n)

I have something strange going on.


I often use FirstSortedValue when making a report builder.

I had a need to make a report builder off of most out data model for other developers who are new to QlikView.

There are 2 tables:

FACT

DIMENSION

I can't figure out the rhym or reason to it but even with all data removed from the data model (ruling out data model issues) it sometimes misbehaves.

In the example I am posting FirstSortedValue($Field,$FieldNo,3) and FirstSortedValue($Field,$FieldNo,4) are null but 1,2,5,6 have values.  That's not possible there could't be a value in FirstSortedValue($Field,$FieldNo,5) if there was none for 3 & 4.

qlik1.pngqlik2.png

Given that I have removed all data from the data model and it still behaves like this I am thinking it's not possible to use FirstSortedValue on $Field if there is more than 1 table being used.  To me that sounds like a bug.

If that's the case I supose I can work around it by loading the field names into a table but I thought this was a clever way with minimum script and code to build a dynamic report builder off the whole data model.

Anyone ever run into trouble using FirstSortedValue($Field,$FieldNo,n)?

1 Solution

Accepted Solutions
Luminary
Luminary

Re: Problem using FirstSortedValue on $Field =FirstSortedValue($Field,$FieldNo,n)

Daniel - $FieldNo is not unique across all the QV tables in your model. For example, if you have more than one table - then $FieldNo starts from 1 instead of starting from last number from previous table. This is the reason why are getting the dash "-" which is the NULL value. So, if there are two field names with same $FieldNo then FirstSortedValue will return the NULL value. I think - this is what is happening. To prove, this is exactly what is happening - create a ListBox with $Table and select only one table then try to check your expression.

Also, I've made a video tutorial on FirstSortedValue on my blog: http://qlikshare.com/tag/firstsortedvalue/

Hope this makes sense.

Cheers,

DV

2 Replies
dgreenberg
Contributor III

Re: Problem using FirstSortedValue on $Field =FirstSortedValue($Field,$FieldNo,n)

Sorry trying to figure out how to move to Qlik

Luminary
Luminary

Re: Problem using FirstSortedValue on $Field =FirstSortedValue($Field,$FieldNo,n)

Daniel - $FieldNo is not unique across all the QV tables in your model. For example, if you have more than one table - then $FieldNo starts from 1 instead of starting from last number from previous table. This is the reason why are getting the dash "-" which is the NULL value. So, if there are two field names with same $FieldNo then FirstSortedValue will return the NULL value. I think - this is what is happening. To prove, this is exactly what is happening - create a ListBox with $Table and select only one table then try to check your expression.

Also, I've made a video tutorial on FirstSortedValue on my blog: http://qlikshare.com/tag/firstsortedvalue/

Hope this makes sense.

Cheers,

DV

Community Browser