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)

[mithilesh17]

Nice explanation n liked that CropField image too on top right

Regards,

Mithilesh

[rwunderlich]

I think the section about FieldIndex() being a Chart Inter-Record Function and the associated "Rules" is incorrect. It may be what the Help says, but I think the Help is wrong.

FieldIndex() is in the  "Inter-Record" group. But it does not share the restrictions of chart only functions like rowno(). FieldIndex can be used in Script. It can be used in Charts. It has no effect on sorting or zero values.

[robert_mika]

You are right Rob.

I always create my documents based (mostly) on Qlik help.

In the help when you look at this function you will find that FieldIndex belongs to Chart Inter-Record and Inter -Record Functions

Moreover in the Charts group there is heading that

"These functions can only be used in chart expressions."

Well, I would not say that TextObject and Button is a Chart but you can still use FieldIndex and FieldName functions there.

I'm going to revise this document.

Is there anyone who can look at this 'Rules''?

[anguila]

Interesting, it might be useful in some scenarios, but I don't get that "They said there is no lookup in Qlik fron end?", What about:

Only({<Name={'John Doe'}>} Helptext)

I can't think in any case where I would use FieldValue and FieldIndex insted of Only for return a value.. Is there?

Regards,

[robert_mika]

Hi David,

Thanks for sharing

There is more than one way to skin the cat...

The bonus example is just to show how to use this function in different way.

[rwunderlich]

I use FieldValue and FieldIndex frequently in script, but don't recall using it the UI. I used to use it for language translation but switched to Set Analysis when that became available.

I happen to be in the middle of this Function Survey project

Help with QV Function Survey

and can tell you thus far 24% of the  responding sites are using FieldIndex in the front end.

Please run the Function Survey yourself if you have not already done so,

[swuehl]

Robert,

be aware that the index used in FieldValue() and returned by FieldIndex() is the index of the distinct value in the specific field (and based on load order). The logic of

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

will break if there are duplicate HelpText values in your table. For example, let's assume also Jenny is related to a Color help text.

Your expression will return the third value from the field, which is Font.

If you can't ensure uniqueness in field values, I strongly recommend not to use these functions to achive the lookup, use the set analysis suggested above instead.

[datanibbler]

Hi,

I do not quite understand. This FieldValue(FieldIndex()) thingy. Maybe someone can explain, that would be great.

From the example, those two columns are in one table - so it's not really a big thing to enter 'John Doe' and return 'Color', is it? Would that also work on fields from two different tables like a real VLOOKUP?

Sorry if I appear kind of limited - but I cannot quite get my head around that.

Thanks a lot!

Best regards,

DataNibbler

P.S.: OK, it does work when you have two tables which are not associated in any way. Then this is really useful.

[robert_mika]

Thank you Stefan for pointing that out.

This was more to show the use of those two functions not to find the best way to get the result  but your comments are very informative.

There are many roads leads to Rome ..

One is good one is even better...

[robert_mika]

Thank you for comments DataNibbler‌

and that you found this functions intresting