Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_klix
Creator II
Creator II

Bug in FieldValue?

There is a sample script onhelp.qlik.com  to test the function "FieldValue". This script shows a strange behavior.

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Inter...

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:

  • root cause is that the first name "Peter" occurs more then one time in the table.
  • in this case it works only for the first occurence of 'Peter' in row 4
  • '6' works fine if used for the column 'last name' with only unique values.
  • of course it is independent from 'Peter', the effect is reproducable with any other duplication.

Can anyone confirm?

 

Basically, this function should be able to deliver the correct any Fieldvalue independent from its content, I assume.

Labels (2)
6 Replies
petter
Partner - Champion III
Partner - Champion III

This is not any problem and hence it doesn't have a "root cause" as you describe it. The script example you refer to is supposed to demonstrate exactly that although it doesn't explain it very well.

This sentence from the online documentation

"If elem_no is larger than the number of field values, NULL is returned."

Should have been

"If elem_no is larger than the number of DISTINCT field values, NULL is returned."


The FieldValue() function only reference the distinct field values of a field. It has been designed that way quite intentionally and it is not a side-effect or bug.

The very fabric and architecture of Qlik and how it handles data in fields is based upon that it both compress and create (sorted) indexes of all the distinct values of any field. The FieldValue() function is looking up/indexing into this distinct value-list in more technical Qlik jargon it is called the "symbol list". Qlik creates these indexes and the invisible helper tables in the background at the very end of a load script execution when it consolidates the final data model for an application.

petter
Partner - Champion III
Partner - Champion III

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...

 

 

michael_klix
Creator II
Creator II
Author

Thanks for the reply. Thats exactly my point. The documention should mention this.
I was looking for a function that can pick a value from a field in a dedicated row during the load.
But seems this is nit possible.

Btw: looking at FieldValueCount, I could not find a difference to count ( distinct). Except for the quotation marks fir the field name.
Is there a relevant one?

petter
Partner - Champion III
Partner - Champion III

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
michael_klix
Creator II
Creator II
Author

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.

 

 

 

petter
Partner - Champion III
Partner - Champion III

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.