article Missing Manual - FieldIndex() in Member Articles

Missing Manual - FieldIndex()

robert_mika — Wed, 22 Apr 2015 12:54:49 GMT

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)

Re: Missing Manual - FieldIndex()

mithilesh17 — Thu, 23 Apr 2015 12:13:48 GMT

Nice explanation n liked that CropField image too on top right

Regards,

Mithilesh

Re: Missing Manual - FieldIndex()

rwunderlich — Thu, 23 Apr 2015 19:19:47 GMT

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.

Re: Missing Manual - FieldIndex()

robert_mika — Thu, 23 Apr 2015 21:29:51 GMT

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''?

Re: Missing Manual - FieldIndex()

anguila — Thu, 23 Apr 2015 22:19:17 GMT

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,

Re: Missing Manual - FieldIndex()

robert_mika — Thu, 23 Apr 2015 22:38:48 GMT

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.

Re: Missing Manual - FieldIndex()

rwunderlich — Thu, 23 Apr 2015 22:41:12 GMT

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,

Re: Missing Manual - FieldIndex()

swuehl — Thu, 28 May 2015 12:55:55 GMT

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.

Re: Missing Manual - FieldIndex()

datanibbler — Thu, 28 May 2015 13:07:37 GMT

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.

Re: Missing Manual - FieldIndex()

robert_mika — Thu, 28 May 2015 13:17:16 GMT

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...

Re: Missing Manual - FieldIndex()

robert_mika — Thu, 28 May 2015 13:19:05 GMT

Thank you for comments DataNibbler‌

and that you found this functions intresting

Re: Missing Manual - FieldIndex()

daniel_kusiak — Tue, 04 Aug 2015 12:45:10 GMT

Hi Robert_Mika,

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

It's a little confusing 🙂

Re: Missing Manual - FieldIndex()

robert_mika — Sat, 08 Aug 2015 06:11:57 GMT

Thank You Daniel.

I have corrected this

Re: Missing Manual - FieldIndex()

cheburashka — Tue, 11 Aug 2015 14:57:54 GMT

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

Re: Missing Manual - FieldIndex()

Tue, 03 Nov 2015 01:12:03 GMT

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.

Re: Missing Manual - FieldIndex()

Chanty4u — Fri, 12 Feb 2016 06:03:29 GMT

nice post

Re: Missing Manual - FieldIndex()

adnan_rafiq — Sat, 16 Apr 2016 09:23:20 GMT

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

Re: Missing Manual - FieldIndex()

robert_mika — Sat, 16 Apr 2016 23:04:25 GMT

This function can be used in a script.

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

Re: Missing Manual - FieldIndex()

adnan_rafiq — Sun, 17 Apr 2016 00:59:37 GMT

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

Re: Missing Manual - FieldIndex()

robert_mika — Sun, 17 Apr 2016 21:44:11 GMT

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');