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

[dgreenberg]

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)?

[rubenmarin]

Hi Daniel, FirstSortedValue() returns Null() when there are more than one value with the same 'sort', can you check if this is what's happening? Maybe 3rd and 4th values are tied in the sort.

[dgreenberg]

I hear you and very good point but I played with the sort order and even when sorted alphabetically where no tie is possible it still has a problem.

If I do nothing but flatten the data model having all fields event the ones from the Dimension table in the Fact table there is no issue.

So again I am thinking maybe there is a bug using FirstSortedValue($Field,$FieldNo,n).

[rubenmarin · Accepted solution]

$FieldNo is assigned by table, I would check the values using a table box, check the option to show system fields (below the list of fields) and add $Field and $FieldNo to the table box.

If you can upload a sample I can give more help instead of guessing like I'm doing right now.

[dgreenberg]

How about that!

The $FieldNo is the same.  It doesn't make sense to me but I guess it's ordianl is based on which table it came from so if just using 1 table FACT there would be no duplication of the $FieldNo but if using 2 tables there could be.

Thanks for the help.  Given this I think I will read the fields into a table and use the table not $Field.

Here I was thinking I was clever.

[rubenmarin]

I'm not sure of what you're trying but, if it helps, you can use other options to retrieve field names, i.e.:

=Subfield(Concat(DISTINCT $Field, ','), ',', 1)

[dgreenberg]

Subfield(Concat(DISTINCT $Field, ','), ',', 2)

Very clever and much better.

The use case is to provide a dynamic straight table chart builder where less skilled developers can pick any field from the data model and add it to the straight table chart.

My dimensions are all as shown below.

The formulas were

FirstSortedValue($Field,$FieldNo,3)   or ,4 ,5,6....

But I am going to change it to your suggestion.