Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More

Missing Manual - FieldValue

cancel
Showing results for 
Search instead for 
Did you mean: 
robert_mika
Master III
Master III

Missing Manual - FieldValue

Last Update:

Apr 20, 2015 11:55:33 PM

Updated By:

robert_mika

Created date:

Apr 20, 2015 11:55:33 PM

Attachments

          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

Comments
datanibbler
Champion
Champion

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
Master III
Master III

Thanks for the comments and ideas.

I will take that for granted...

jagan
Luminary Alumni
Luminary Alumni

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
Master III
Master III

Thank you for the comments Jagan.

Appreciate that.

Chanty4u
MVP
MVP

Simple nd nice

Chanty4u
MVP
MVP

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

No worries, just found it.

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


Cheers!

Version history
Last update:
‎2015-04-20 11:55 PM
Updated by: