6 Replies Latest reply: Oct 7, 2013 10:31 AM by Peter Schulz RSS

    Merge of 2 Tables

    Peter Schulz

      Hi,

      I have a nasty problem in my script. I think I have to do this with 2 steps but, I'm not sure. This drives me crazy...

       

      2 tables which look like this:

      TBL1:

          Key1,Key2,Time

          100,1, 10

          100,2, 15

          100,3, 20

      TBL2:

          Key1,Key2,Time

          100,1, 10

          100,2

          100,3

          100,4,15

          200,1,10

          200,2

          200,3,30

       

      Empty fields of a table should be filled (normally only TBL2 has gaps) and no duplicate Values(Key1&Key2) at all.

       

      Final table would look like this:

       

      FINALTBL:

      Key1,Key2,Time

          100,1, 10

          100,2, 15

          100,3, 20

          100,4,15

          200,1,10

          200,2

          200,3,30

       

      I hope somebody could help me. I tried this earlier. And thought I could just make a cut a specific Key1 and then concatenate the new keys. But this is not possible.

       

      Thank you in advance.

       

      Peter

        • Re: Merge of 2 Tables
          Michael Solomovich

          Peter, try this:

           

          FINALTBL:
          LOAD DISTINCT
          Key1,
          Key2,
          Time,
          Key1 & Key2 as Key
          FROM TBL1;

           

          CONCATENATE (FINALTBL) LOAD DISTINCT
          Key1,
          Key2,
          Time
          FROM TBL2
          WHERE not exists(Key, Key1 & Key2);

           

          DROP FIELD Key;

           

          It should work with the data in your example.  I can't tell if it covers all possible situations, the requirement is not quite clear to me.

           

          Regards,
          Michael

            • Re: Re: Merge of 2 Tables
              Peter Schulz

              HI Michael,

               

              thank you for your reply.

               

              It works very well, it's a lot better than my "solution". Thank you very much.

               

              The requirement is to fill missing field values in TBL2 with values from TBL1. TBL1 is old data from a database. The fields exist in TBL2 but the older ones are empty(but not all). Now there is an interface and the data is transferred every day into a datawarehouse. But the values from older datasets cannot be transferred into the dwh (for several reasons). So I have to bring them together in qlikview.

               

              I hope this clears things a bit. I will play around with it in the real environment tomorrow.

               

              I created a qwv...see attachment.

               

              Big thanks and have nice day.

            • Re: Merge of 2 Tables
              Peter Schulz

              Hi again,

               

              Right on my way home the solution came to me. (I think so)

              See attached file.

               

              I think 3 Steps are necessary:

              1. Generate one composite key from key1 and key2 and rename the fields in one table, except the key field

              2. outer join or right join  ... in my case both works.. Anyone more experienced here who can tell me which join i should use?

              3. Loading fields in a seperate table using IF statements

               

              I don't know if this is the best solution and I have to try it in a real environment. But I think it will work as supposed. If anyone has a smarter/better solution...you are very welcome!

               

              Regards,

               

              Peter

                • Re: Merge of 2 Tables
                  Michael Solomovich

                  My solutions also come often on the way home :-)

                  You got it right - the composite key is The Key.

                  Outer join will guarantee that you get the rows from both tables, if this is what you want.  The word "outer" is optional, in QV you can use "join", it is the same as "outer join".  Right join removes data from the first table if they not exist in the second.  In your case it is the same because all keys from the first table exist in the second.

                   

                  Regards,
                  Michael