3 Replies Latest reply: Jul 31, 2012 9:12 AM by At titude RSS

    How to Match values from a List?

    Jens Ophalvens

      Dear,

       

      I am looking for a way to lookup a value in a list in Qlikview (like a vlookup in Excel).

      I tried with the Match function but this doesn't seem to work.

       

      I have 2 table's that both contain a field ID (ID1 and ID2). Want I want to do is check if the value ID2 appears in the list of ID1. If so I would want it to return a 1. Like this I will be able to see which ID's got migrated correctly and which ones didn't.

       

      Does anyone know how to achieve this with Qlikview?

       

      Thanks in advance!

      Kind regards,

      Jens

        • How to Match values from a List?

          You can use the Where exists in a new table (maybe a temporal one) that concatenate the two tables and that way you can know the ID that exist in the ID1 from the ID2: I make something like this:

           

          ID1:

          load * inline

          [

          ID1

          1

          2

          3

          4

          5

          6

          ];

           

           

          ID2:

          load * inline

          [

          ID2

          1

          2

          3

          4

          5

          6

          7

          8

          9

          ];

           

          NoConcatenate

          Validate_ID:

          LOAD Distinct ID1 as IDs Resident ID1;

                    inner join

          LOAD Distinct ID2 as IDs Resident ID2

          where Exists(ID1,ID2);

           

          Hope it Helps.

          regards

          • Re: How to Match values from a List?

            Juan is on the right track, but it sounds like you are looking for a flag instead of limiting the load.

             

            ID1:

            LOAD * INLINE [

                 ID1,

                 1,

                 2,

                 3,

                 4,

                 5

            ];

             

             

            ID2_temp:

            LOAD * INLINE [

                 ID2,

                 1,

                 2,

                 3,

                 4,

                 5

            ];

             

             

            ID2:

            LOAD ID2

                 ,If( Exists(ID1,ID2),1,0) as ID1_Flag

            RESIDENT ID2_temp;

             

             

            DROP TABLE  ID2_temp;

            • Re: How to Match values from a List?

              Hope it helps!

               

              Production:
              LOAD Field1,
                   Field2,
                   Field3,
                   Field1&Field2&Field3 AS %Key1
              FROM
              Table1.qvd
              (qvd);
              
              Development:
              NoConcantenate
              LOAD Field1,
                   Field2,
                   Field3,
                   Field1&Field2&Field3AS %Key2 
              FROM
              Table2.qvd
              (qvd)
              WHERE NOT EXISTS(%Key1,Field1&Field2&Field3)
              ;
              Drop Table Production;