5 Replies Latest reply: May 31, 2011 11:32 PM by Kaushal Mehta RSS

    Find a Value in a Table

      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
          Johannes Sunden

          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

              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

                  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
                      Johannes Sunden

                      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
                        Kaushal Mehta

                        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)