topic Find a Value in a Table in QlikView

Find a Value in a Table

Tue, 31 May 2011 22:47:17 GMT

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.

Re: Find a Value in a Table

Anonymous — Wed, 01 Jun 2011 00:33:39 GMT

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

Re: Find a Value in a Table

Wed, 01 Jun 2011 02:16:56 GMT

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.

Find a Value in a Table

Wed, 01 Jun 2011 03:04:17 GMT

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.

Find a Value in a Table

Anonymous — Wed, 01 Jun 2011 03:28:09 GMT

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.

Find a Value in a Table

kaushalview — Wed, 01 Jun 2011 03:32:05 GMT

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)

Re: Find a Value in a Table

Andrew_Maynard1 — Wed, 29 Mar 2023 15:30:32 GMT

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