Missing Manual - FieldIndex()

[robert_mika]

         

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

Please see below and enjoy responsibly...

FieldIndex()

  This function belongs to two groups Chart Inter Record Functions

  and Inter-Record functions.

Returns the position of the field value value in the field fieldname (by load order). If value cannot be found among the field values of the field fieldname, 0 is returned. Fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes.

Data Model
(Copy and paste below code into the edit script window and reload)

LOAD * inline [

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

]

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

Returns position from value specified by second criteria (value)

• Returns positions of value being searched.
• field_name must be given as a string value (enclosed with single quotes ('))
• If value does not exist within column, 0 is returned

Syntax:

field_name - the name of your field/column

value - the value for which you want to know the position

Example

fieldIndex( 'Name', 'John Doe' )

Bonus

They said there is no lookup in Qlik front end?

Think again ...

=FieldValue('Helptext',FieldIndex('Name', 'John Doe'))

by wrapping FieldIndex with FieldValue we can return the unknown value of the 'HelpText' column that corresponds with the given value 'John Doe' of the Name column.

Feeling Qlikngry?

How To /Missing Manual(20+ articles)

[daniel_kusiak]

Hi Robert_Mika,

In inline there is field name HelpText, in expression there is Helptext.

It's a little confusing 🙂

[robert_mika]

Thank You Daniel.

I have corrected this

[cheburashka]

Hello Robert_Mika,

I did some rephrasing in a new version. Hope you don't mind.  If not you can always turn back the version.

Keep up the good work. Much appreciated.

,KR Koen

[#5]

Great for formula explanation.

Thanks Rob.

Qlikview help manual really needs to be upgraded to be easier to understand and include more example added in.

[Chanty4u]

nice post

[adnan_rafiq]

Can this function be used to find the Index of a field in load script. ??

[robert_mika]

This function can be used in a script.

Could you describe your problem/provide data set and required result?

[adnan_rafiq]

Robert my problem is i want to find the index number of  a value in a column in a table and save that into variable. Here is the script, is this right?

LINE_ITEMS:

LOAD [LINE_ITEMS]  FROM

[some path.qvd]

(qvd);

STORE LINE_ITEMS into LINE_ITEMS.qvd(qvd);

let vL.Fields=FieldIndex(LINE_ITEMS,'% of Returns Warehouse');

The problem is that this variable returns NULL.

I want your advice on the scenario, When I get this index where the value''% of Returns Warehouse'' is present, I want to the load the table only up till that point. Is that possible??. I would really appreciate it

[robert_mika]

Does this help you?

LINE_ITEMS:

load * inline [

LINE_ITEMS,

Tax,

VAT,

% of Returns Warehouse,

Sales,

Orders

]

;

T2:

LOAD

FieldIndex('LINE_ITEMS','% of Returns Warehouse') as vl

resident

LINE_ITEMS;

drop table LINE_ITEMS;

let vl_Field=Peek('vl', 0, 'T2');