Missing Manual - FieldValue

[robert_mika]

                 

    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.

[datanibbler]

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)

[robert_mika]

Thanks for the comments and ideas.

I will take that for granted...

[jagan]

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.

[robert_mika]

Thank you for the comments Jagan.

Appreciate that.

[Chanty4u]

Simple nd nice

[Chanty4u]

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 ?

[#8]

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

[#9]

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

    three

];

This is NULL:

=FIELDVALUE('HELPTEXT', 4)

This is "three":

=FIELDVALUE('HELPTEXT', 3)

[jaygarcia]

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

    three

];

[jaygarcia]

No worries, just found it.

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

Cheers!