topic FieldValue returning NULL when a previous value reoccurs in QlikView

FieldValue returning NULL when a previous value reoccurs

marcelo_7 — Sun, 13 May 2012 01:31:11 GMT

Hello,

I'm new to Qlikview and this might have a very simple solution but I'm really struggling.

I'm extracting values from two fields (city, region) and some cites are in the same region.

Example:

City Region

City1 RegionA

City2 RegionA

Script:

LOAD City,

Region

FROM

(biff, embedded labels, table is Sheet1$);

let x = FieldValue('Region', 1);

let y = FieldValue('Region', 2);

In debug this would give me x = RegionA but y = <NULL>.

If I instead used the following

let x = FieldValue('Region', 1);

let y = FieldValue('Region', 1);

This would give me x = RegionA and y = RegionA

In the reference for FieldValue NULL is given when the number is larger than the rows in the document and that's not the case, please help! Thanks!

Re: FieldValue returning NULL when a previous value reoccurs

swuehl — Sun, 13 May 2012 08:29:34 GMT

Are you sure about the reference statement?

I believe the fieldvalue index references the distinct values of that field in load order, it has nothing to do with table rows.

As far as I remember the index starts with one and you only have one distinct value in your table, RegionA.

So that is why your code using index = 1 retrieves the value and your code using index=2 does not - there is no second distinct value.

Check the number of distinct values in your field using

Let vNumValuesRegion = FieldValueCount('Region');

Hope this helps

Stefan

edit: corrected index start

Re: FieldValue returning NULL when a previous value reoccurs

marcelo_7 — Sun, 13 May 2012 12:33:55 GMT

Let vNumValuesRegion = FieldValueCount('Region');

vNumValuesRegion is 1 even though I have two rows with the same name so it's like you said. I found a function peek() which does not care about distinct values. Thank you!

FieldValue returning NULL when a previous value reoccurs

swuehl — Sun, 13 May 2012 13:37:41 GMT

Right, Peek() is the function to use if you want to address table values (in contrast to fieldvalue() if you want to address field values, as mentioned, this is a big difference).

Note the inconsistency in QV, peek() index starts with zero for the first row...

FieldValue returning NULL when a previous value reoccurs

marcelo_7 — Sun, 13 May 2012 21:09:55 GMT

Oh I think I get it, FieldValue is for retrieving the column name and peek is for the actual rows? Is that correct? If so that would explain why it ignores recurring fields because there can only be one instance of a column for a table to make sense. Otherwise I don't understand what the purpose of FieldValue is since peek can do the same and more.

FieldValue returning NULL when a previous value reoccurs

swuehl — Sun, 13 May 2012 21:41:05 GMT

A field can contain values of different tables (if used as key field). Fieldvalue() will access all distinct values of that field (think of it more like the list box object) while peek() will only access values of one table (if you don't state the table name, the current table used in the load will be used) and it does so row based, so also retrieving duplicate values (think of it more like a table object, but showing all rows (like using rowno() as dimension).

Hence, fieldvalue() and peek() both have their meaningful use, but in your case, I guess you want to use peek().

FieldValue returning NULL when a previous value reoccurs

marcelo_7 — Sun, 13 May 2012 21:51:51 GMT

Your list box/table object analogy made it easy to understand. Thank you!