Find a Value in a Table

[#1]

I need to find a value in a Table by passing the user login Id.

ex:

Security:

UserId,category

ntest,star

ktest,moon

If I have a table with fields UserId and category as shown above, I need to know the Category by passing the User Id. i am using below formula in a expression and it does not work.

=Fieldvalue('Security.category,FieldIndex('Security.UserId','ntest'))

I am trying to find the Category of the user opening the qlikview application and would like to set some default selections for different fields.  Please suggest me how can i do this?

Thanks,

Naveen.

[Anonymous]

Naveen,

Just looking at the expression you seem to have left out a ( ' ) after the Security.category. Wanted to make sure that this was not the issue.

Otherwise the approach looks ok to me. Also, are your fieldnames qualified? otherwise the name of them will be category and UserId instead of Security.category and Security.UserId.

Actually, did a quick test, and it works fine here. Example attached

[#3]

Thanks Johannes. That was a typo error. I have it correct in my application. Actually it works with the given test data, but I have a table caled security that has about 600 rows of data and somehow i think the FieldIndex value that it retrieves is not correct, so that's why Field value returned by the expression is not correct value.. Do you think i need to sort the table or do anything on the table so that it returns correct FieldIndex value. I am loading this table from Oracle Database into Qlikview.

Thanks,

Naveen.

[#4]

Please update your example with the below test data and you will understand my issue.

LOAD * INLINE [

    UserId, category

    ntest, star

    ktest, moon

    dtest,

    rtest,

    mtest,sun

];

Use the below expression and it will not return "sun".

=Fieldvalue('category',FieldIndex('UserId','mtest'))

I think this is happening because FieldIndex Value for mtest returns 5, whereas "sun" is at position 4 according to load order. Hope I had clearly explained my issue.

How do i return "sun" for mtest?

Thanks,

Naveen.

[Anonymous · Accepted solution]

Right. In the example above, you have two blank values that will share one position.

So FieldIndex('UserId','mtest') will return 5, while sun is in position 4 of the category field.

I think that if you're getting into something more complex here it might be better to re-evaluate what you're trying to achieve with this? Maybe there's a more straight forward approach.

For example:

With the table that you have in your latest example, you could use an expression like:

=MaxString({$<UserId={'mtest'}>} category)

This would give you 'sun' as expected.

[kaushalview]

Hi,

Take a resident of the table and make a new table as below

Load *,UserId&'_'&category as LinkField

resident A;

Drop table A;

Now in the Expression

Subfield(Fieldvalue('LinkField',fieldindex('UserID','mtest')),'_',2)

[Andrew_Maynard1]

I tried using "Field value" ... the answer you need is "LookUp" ! 

Eg Referencing data ... USE lookUp !!
Let vLCT = Lookup('LAST_CHANGE_TIMESTAMP','TABLE_NAME','NATLOS','SYSVIEW');