Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
There is a sample script onhelp.qlik.com to test the function "FieldValue". This script shows a strange behavior.
If you use this script in Sense or View, it first demonstrates, that with 2nd Parameter'7', it delivers null as the table has only 6 rows of data. But the same happens for '6'.
Some experiments lead me to the following conclusion:
Can anyone confirm?
Basically, this function should be able to deliver the correct any Fieldvalue independent from its content, I assume.
Elaborating on what I wrote previoiusly...
To add insult to injury the documentation has the FieldValue(), FieldIndex() and the and corresponding FieldValueCount() categorized under the section INTER-RECORD FUNCTIONS and they are not in any way inter-record nor inter-row related functions. They deal purely with a single field at a time and disregards how the field participates in any row/record...
IT IS POSSIBLE - you do have many other real inter-record functions available to look up field values in other rows/records actually:
Above(), Below(), Top(), Bottom(), Before(), After(), First(), Last() can all be used for this purpose.
Beware that they all relate to the dimensions use in the chart/table so if you want to navigate the entire chart/table and not just within the last dimension you will have to use TOTAL qualifier inside the inter-record function...
BTW: didn't understand what you asked in your BTW question ... could you rephrase or clarify that?
Please click the like button or heck if the response was correct you could even mark it as a correct solution. Believe me - it will be greatly appriciated by the contributors ... We love likes as much as anyone posting anything on social media |
Hi Petter,
indeed the mentioned inter-record functions exist, but only for charts. What I was looking for was a fuction for the SCRIPT that allows to select a value for any colums from any row.
Example:
Take the row with the 3rd appearance of "Peter" in the field "FIRSTNAME" according to load order and give me the content of the field "SALARY".
This is just an example but beleive me I have a case where a similar problem occurs 🙂
So the FieldValue() indeed works only on distinct values like other functions (lookup(), etc) that only pick the first appearance from the top of the table. Should be mentioned in help, I think.
According to my BTW:
Both in Script and Chart we can use COUNT( DISTINCT ...) to count the distinct values of a field. The same can be done with FieldValueCount(...).
However meanwhile I found the difference: FieldValueCount refers always to the raw data and not to the current selection.
In the load script you have Peek(), Lookup() and ApplyMap().
And when they don't serve the purpose the natural thing is to either do a join or just make sure that the tables in question have the right association with a key (or a concatenated key) so you will get a "associated look-up" in the a chart in the UI when using fields across the two tables.