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
FieldValue.qvw 130.8 K
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.
Please update your example with the below test data and you will understand my issue.
LOAD * INLINE [
Use the below expression and it will not return "sun".
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?
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.
With the table that you have in your latest example, you could use an expression like:
This would give you 'sun' as expected.