3 Replies Latest reply: Nov 26, 2011 11:25 AM by Stefan Wühl RSS

    Lookup or matching function

      Hi,

       

      I would like to know whether it is possible, and how, to lookup values or text.

       

      What i am specifically lookin for, is a way to load an excel file or such, with a list of ids, lets call them 1,2,3,4.

      Qv is then to look in a masterdatabase for these ids, and for each id return a list of subfunctions specified in the masterdb.

       

      Pease let me know if you need more info.

       

      /Rune

        • Lookup or matching function
          Stefan Wühl

          It probably depends on your concrete data model and what you want to achieve, I can think of at least 3 functions / methods that may help you:

           

          1) Check out the lookup() function in the help, though this is probably not what you want.

           

          2) You could use a mapping table (Check out Mapping Load and applymap() function in the help).

           

          These two ideas are more for 1:1 relations, if you want to have 1:n relationship (1 function to n subfunctions), you could also just load the two tables (your index table and the master table, and link both using the index).

           

          So an actually lookup will be performed during runtime dependent on the selection on this index field.

           

          If you could post a small sample (just some lines) of your data and what the expected outcome is, I believe someone here in the forum is able to help you pretty fast.

           

          Regards,

          Stefan

            • Re: Lookup or matching function

              I have attached two files.

               

              What I want for qlik, is for it to look up the Ids in look.xlsx in master.xlsx, and return the value for each of f1,f2,f3,f4.

               

              The original DB isn't contained in excel format and is about 100.000 lines, why a simple lookup function in excel is way to time consuming.

               

              /Rune

                • Lookup or matching function
                  Stefan Wühl

                  I think you could just use something like

                   

                  LOOK:

                  LOAD ID

                  FROM

                  Look.xlsx

                  (ooxml, embedded labels, table is Sheet1);

                   

                  MASTER:

                  LOAD ID,

                       F1,

                       F2,

                       F3,

                       F4

                  FROM

                  MASTER.xlsx

                  (ooxml, embedded labels, table is Sheet1) where Exists(ID);

                   

                  or directly join the second load:

                  LOOK:

                  LOAD ID

                  FROM

                  Look.xlsx

                  (ooxml, embedded labels, table is Sheet1);

                   

                  MASTER:

                  Left Join (LOOK) LOAD ID,

                       F1,

                       F2,

                       F3,

                       F4

                  FROM

                  MASTER.xlsx

                  (ooxml, embedded labels, table is Sheet1) where Exists(ID);

                   

                  Regards,

                  Stefan