Qlik Community

QlikView Documents

Documents for QlikView related information.

Missing Manual - FieldValue

Missing Manual - FieldValue

          1f4fe19.jpg       

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

Please see below and enjoy responsibly...

FieldValue()


2015-04-21 04_31_52-field - Google Search.png


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:

2015-04-21 04_50_44-New Text Object.png

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

elem_no - position from the top

Example



fieldvalue( 'Helptext', 5 )

2015-04-21 22_17_42-Microsoft Excel - Book1.png

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.

2015-04-21 22_45_42-Microsoft Excel - Book1.png

Attachments
Comments
datanibbler
Esteemed Contributor

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)

Thanks for the comments and ideas.

I will take that for granted...

MVP
MVP

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.

Thank you for the comments Jagan.

Appreciate that.

sureshqv
Esteemed Contributor III

Simple nd nice

sureshqv
Esteemed Contributor III

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 ?fieldvalue-error.PNG

Not applicable

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
Not applicable

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
New Contributor III

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
New Contributor III

No worries, just found it.

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


Cheers!

Version history
Revision #:
1 of 1
Last update:
‎04-20-2015 11:55 PM
Updated by: