article Missing Manual - FieldValue in Member Articles

Missing Manual - FieldValue

robert_mika — Tue, 21 Apr 2015 03:55:33 GMT

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

FieldValue()

This function belongs to Inter Record Functios and has few rules:

Sorting on y-values in charts or sorting by expression columns in straight tables is not allowed when Chart Inter Record functions are used in any of the chart's expressions. These sort alternatives are therefore automatically disabled.
Suppression of zero values is automatically disabled when this functions is used

Data Model

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

HelpText,	Name
Block,	Bob
Color,	John
Topics,	Jenny
Font,	Curt
Data,	Martin
Group,	Mirek
Total,	Stan

]

Now create Straight table and remember to sort dimension and expression by load order

Returns values from position specified by second criteria (elem_no)

Works only with distinct value
field_name must be given as a string value (enclosed with single quotes ('))
If elem_no is larger than the number of field values, NULL is returned.

Syntax:

Field_name - is a name of of your field(column)

elem_no - position from the top

Example

fieldvalue( 'Helptext', 5 )

Bonus

By using:

=FieldValue('Name', RowNo()+1)

=FieldValue('Name', RowNo()-1)

you can shift values one row below/or above current one loosing the first or last value.

Re: Missing Manual - FieldValue

datanibbler — Wed, 22 Apr 2015 13:02:32 GMT

Good one!

From the help_file I read, however, that the function returns the n-th DISTINCT value from any column - so shifting values one row above or below would only work well if you have zero duplicates in that column - oh, I just realize, you do state that.

Anyway, I think it would be great if all these blogs on little_known functions in QlikView (by yourself and by others) could be made into one document (word, epub or anything - I'd love epub) so one can have all that information together in one place and browse it at their leisure.

(I think there are RSS_feads for these blogs, no? So, using calibre or something, they could all be made into epubs quite easily - unfortunately I have no computer of my own currently and I cannot install anything on this one)

Re: Missing Manual - FieldValue

robert_mika — Wed, 22 Apr 2015 15:22:03 GMT

Thanks for the comments and ideas.

I will take that for granted...

Re: Missing Manual - FieldValue

jagan — Thu, 23 Apr 2015 02:20:34 GMT

You are doing great job Robert_Mika by explaining this type of functions in Qlikview, which the Qlikview manual itself has very less explanation.

Keep going.

Re: Missing Manual - FieldValue

robert_mika — Thu, 23 Apr 2015 02:54:58 GMT

Thank you for the comments Jagan.

Appreciate that.

Re: Missing Manual - FieldValue

Chanty4u — Thu, 14 Jul 2016 07:00:17 GMT

Simple nd nice

Re: Missing Manual - FieldValue

Chanty4u — Thu, 14 Jul 2016 07:05:55 GMT

Hi mika,

Why my Field value output giving he result as before value .EX . : if i given lik below

=FieldValue('HELPTEXT',4) ---- result givng as 3rd value ?

Re: Missing Manual - FieldValue

Wed, 24 Aug 2016 19:58:46 GMT

I wasn't sure if FieldValue only worked on DISTINCT values in a field;

Because Robert said this:

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

...but shouldn't it be this:

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

Maybe you mean this statement was supposed to be a blanket statement to cover that:

Works only with distinct value

Re: Missing Manual - FieldValue

Wed, 24 Aug 2016 20:20:40 GMT

For my sake, this is true:

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

Example:

LOAD * INLINE [

HELPTEXT

one

two

three

];

This is NULL:

=FIELDVALUE('HELPTEXT', 4)

This is "three":

=FIELDVALUE('HELPTEXT', 3)

Re: Missing Manual - FieldValue

jaygarcia — Thu, 23 Feb 2017 15:15:21 GMT

Hi guys,

What would be the alternative if you really need the specific 4th item from HELPTEXT below if you can't use FieldValue (because it returns null)? (A case could be when you have the LOAD in a For-Next loop)

Example:

LOAD * INLINE [

HELPTEXT

one

two

three

];

Re: Missing Manual - FieldValue

jaygarcia — Thu, 23 Feb 2017 15:24:34 GMT

No worries, just found it.

let vHelpText = Peek('HELPTEXT',3, 'Example'); // 0 denotes the first record

Cheers!