FieldValue returning NULL when a previous value reoccurs

[marcelo_7]

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!

[swuehl]

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

[marcelo_7]

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!

[swuehl]

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

[marcelo_7]

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.

[swuehl · Accepted solution]

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

[marcelo_7]

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