5 Replies Latest reply: Jan 3, 2013 5:02 AM by Snehal Nabar RSS

    Merging Data from Multiple Tables

      How to merge data from multiple Excel spreadsheets using QlikView.

      I'm sure it can also be done within Excel -- thought I would use QlikView to increase familiarity with the tool's features.

       

      I have 2 Excel tables.

       

      Table 1 has fields:  SSN FIRST LAST CITY STATE

      Table 2 has fields   SSN FIRST LAST SALARY HIRE DATE

       

      I would like to combine, append, join (which ever term is accurate) the SALARY and HIRE DATE fields to the first Table.

       

      Ideas and best practices please? Thank you.

       

      Proper way to load the data

      Advisable ways to display (table box, multibox, list box, etc)

       

      Thank you.

        • Merging Data from Multiple Tables

          Is SSN a key? If so comment out FIRST and LAST in second table and then join them.

          Join adds columns, concatenate adds rows.

          • Re: Merging Data from Multiple Tables
            Jean-Jacques Jesua

            If you want to merge 2 tables use a join condittion.

            Look at the attachment.

             

            The merge is done thanks your common field. In your case SSN,  FIRST, LAST

             

            So the syntax looks like

             

            Table:

            Load  ...

            From  excelFile1 ;

             

            Join(Table)

            Load ...

            From excelFile2 ;

             

            The second part of your question depends of what you want to do.

            If you want to export the combine file in excel,  go with  a table box.

            List Box is used to make some filters in a chart

             

            JJ

            • Merging Data from Multiple Tables

              you can do the join like this:

              a:

              load * from tablename;

              join(a)

               

              b:

              load * from tablename;

              > it will generate one table i.e a with the data i.e common to both the tables in addition to records that is not common.

               

              a:

              load * from tablename;

              concatenate

               

              b:

              load * from tablename;

               

              >it will append the records of b in table a with null values.

              note: qlikview join is outer join unless specified.


              hope this makes you understand the concept of join and concatenate.

              • Re: Merging Data from Multiple Tables
                Snehal Nabar

                Hi alwindham,

                 

                I would say, if most of the fields are same between the tables use concatenate.

                But, make sure you use the keyword Concatenate between the tables in the script.Or else it will result in creating

                synthetic key and you will end up having three tables in the data model.

                In your case, also identify the primary(unique) key between the tables.

                 

                Hope this helps,

                 

                Regards,

                Snehal Nabar