Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)?
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
Sorry trying to figure out how to move to Qlik
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