9 Replies Latest reply: Jul 5, 2013 7:58 AM by Prem Kumar Thangallapally RSS

    Concatenate Multiple Tables in Script

      I'm trying to load 5 tables into QlikView, all with the same column names so I'm choosing to concatenate them. However, when I try to do this, I get 2 tables - one called StaffRecord and one called StaffRecord-1. Can't for the life of me understand why.

       

      StaffRecord:
      LOAD Employee as EmployeeId,
                'Yearly' As [Frequency],
                if(not Status = '',1,0) as EntryExists,
                if(Status = 'Completed',1,0) as Completed,
                if(Status = 'Confirmed',1,0) as Booked,
                if(Status = 'Did Not Attend',1,0) as DNA,
                if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                [Date Placed],
                [Course Name] as CourseName,
                Status as CourseStatus
      FROM [spread1.xlsx]
      (ooxml, embedded labels, header is 1 lines);
      StaffRecord:
      concatenate (StaffRecord)
      LOAD Employee as EmployeeId,
                '2 Yearly' As [Frequency],
                if(not Status = '',1,0) as EntryExists,
                if(Status = 'Completed',1,0) as Completed,
                if(Status = 'Confirmed',1,0) as Booked,
                if(Status = 'Did Not Attend',1,0) as DNA,
                if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                [Date Placed],
                [Course Name] as CourseName,
                Status as CourseStatus
      FROM [spread2.xlsx]
      (ooxml, embedded labels, header is 1 lines);
      StaffRecord:
      concatenate (StaffRecord)
      LOAD Employee as EmployeeId,
                '3 Yearly' As [Frequency],
                if(not Status = '',1,0) as EntryExists,
                if(Status = 'Completed',1,0) as Completed,
                if(Status = 'Confirmed',1,0) as Booked,
                if(Status = 'Did Not Attend',1,0) as DNA,
                if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                [Date Placed],
                [Course Name] as CourseName,
                Status as CourseStatus
      FROM [spread3.xlsx]
      (ooxml, embedded labels, header is 1 lines);
      StaffRecord:
      concatenate (StaffRecord)
      LOAD Employee as EmployeeId,
                '4 Yearly' As [Frequency],
                if(not Status = '',1,0) as EntryExists,
                if(Status = 'Completed',1,0) as Completed,
                if(Status = 'Confirmed',1,0) as Booked,
                if(Status = 'Did Not Attend',1,0) as DNA,
                if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                [Date Placed],
                [Course Name] as CourseName,
                Status as CourseStatus
      FROM [spread4.xlsx]
      (ooxml, embedded labels, header is 1 lines);
      StaffRecord:
      concatenate (StaffRecord)
      LOAD Employee as EmployeeId,
                '5 Yearly' As [Frequency],
                if(not Status = '',1,0) as EntryExists,
                if(Status = 'Completed',1,0) as Completed,
                if(Status = 'Confirmed',1,0) as Booked,
                if(Status = 'Did Not Attend',1,0) as DNA,
                if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                [Date Placed],
                [Course Name] as CourseName,
                Status as CourseStatus
      FROM [spread5.xlsx]
      (ooxml, embedded labels, header is 2 lines);
      
      

       

      Any help is appreciated! Cheers.

        • Re: Concatenate Multiple Tables in Script
          Jonathan Dienst

          Hi

           

          Cant see anything wrong with your code. What is loaded into the second table? Is it just from the last spreadsheet? I noticed a different header setting for that load.

           

          Also, the table names in the second and subsequent loads are redundant, but I dont think they are causing the problem.

           

          You dont have a Qualify statement somewhere?

           

          Just some thoughts. Hope that helps

          Jonathan

          • Re: Concatenate Multiple Tables in Script

            I'm not sure, but this is the only logical explanation I can think of:

             

            You are concatenating the StaffRecord table to itself. If my memory serves me correctly, QV has to make a 'copy' of StaffRecord table while it's concatenating the data to the actual StaffRecord table. That's why you are left with 2 tables, the orginal StaffRecord and the 'copy'.

              • Re: Concatenate Multiple Tables in Script

                Ah, that does make sense. However, it leads me to think that there must be a solution. In SQL, you'd do something like:

                 

                INSERT INTO TEST_TABLE(A, B, C)

                SELECT A, B, C FROM TABLE1

                UNION ALL

                SELECT A, B, C FROM TABLE2

                UNION ALL

                SELECT A, B, C FROM TABLE3

                 

                or even

                 

                INSERT INTO TEST_TABLE(A, B, C)

                SELECT A, B, C FROM TABLE1

                 

                INSERT INTO TEST_TABLE(A, B, C)

                SELECT A, B, C FROM TABLE2

                 

                INSERT INTO TEST_TABLE(A, B, C)

                SELECT A, B, C FROM TABLE3

                 

                 

                My SQL might be a little rusty, but that's about right.

                 

                Can I create an empty table to insert the concatenated tables into? I must be doing it wrong somewhere. I'll probably post this answer on a public forum when I solve it, as it must be a common issue.

                  • Re: Concatenate Multiple Tables in Script

                    Ok, so I'm not sure how or why, but I implemented the below and it got the result I wanted.

                     

                    StaffRecord:
                    LOAD Employee as EmployeeId,
                              'Yearly' As [Frequency],
                              if(not Status = '',1,0) as EntryExists,
                              if(Status = 'Completed',1,0) as Completed,
                              if(Status = 'Confirmed',1,0) as Booked,
                              if(Status = 'Did Not Attend',1,0) as DNA,
                              if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                              [Date Placed],
                              [Course Name] as CourseName,
                              Status as CourseStatus
                    FROM [spread1.xlsx]
                    (ooxml, embedded labels, header is 1 lines);

                     

                    RENAME TABLE StaffRecord TO StaffRecord_ALL;


                    StaffRecord:
                    LOAD Employee as EmployeeId,
                              '2 Yearly' As [Frequency],
                              if(not Status = '',1,0) as EntryExists,
                              if(Status = 'Completed',1,0) as Completed,
                              if(Status = 'Confirmed',1,0) as Booked,
                              if(Status = 'Did Not Attend',1,0) as DNA,
                              if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                              [Date Placed],
                              [Course Name] as CourseName,
                              Status as CourseStatus
                    FROM [spread2.xlsx]
                    (ooxml, embedded labels, header is 1 lines);

                     

                    RENAME TABLE StaffRecord TO StaffRecord_ALL;


                    StaffRecord:

                    LOAD Employee as EmployeeId,
                              '3 Yearly' As [Frequency],
                              if(not Status = '',1,0) as EntryExists,
                              if(Status = 'Completed',1,0) as Completed,
                              if(Status = 'Confirmed',1,0) as Booked,
                              if(Status = 'Did Not Attend',1,0) as DNA,
                              if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                              [Date Placed],
                              [Course Name] as CourseName,
                              Status as CourseStatus
                    FROM [spread3.xlsx]
                    (ooxml, embedded labels, header is 1 lines);

                     

                    RENAME TABLE StaffRecord TO StaffRecord_ALL;


                    StaffRecord:
                    LOAD Employee as EmployeeId,
                              '4 Yearly' As [Frequency],
                              if(not Status = '',1,0) as EntryExists,
                              if(Status = 'Completed',1,0) as Completed,
                              if(Status = 'Confirmed',1,0) as Booked,
                              if(Status = 'Did Not Attend',1,0) as DNA,
                              if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                              [Date Placed],
                              [Course Name] as CourseName,
                              Status as CourseStatus
                    FROM [spread4.xlsx]
                    (ooxml, embedded labels, header is 1 lines);

                     

                    RENAME TABLE StaffRecord TO StaffRecord_ALL;


                    StaffRecord:
                    LOAD Employee as EmployeeId,
                              '5 Yearly' As [Frequency],
                              if(not Status = '',1,0) as EntryExists,
                              if(Status = 'Completed',1,0) as Completed,
                              if(Status = 'Confirmed',1,0) as Booked,
                              if(Status = 'Did Not Attend',1,0) as DNA,
                              if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
                              [Date Placed],
                              [Course Name] as CourseName,
                              Status as CourseStatus
                    FROM [spread5.xlsx]
                    (ooxml, embedded labels, header is 2 lines);

                     

                    RENAME TABLE StaffRecord TO StaffRecord_ALL;

                • Re: Concatenate Multiple Tables in Script
                  Prem Kumar Thangallapally

                  Having  same fields in all tables  then qlikview automatically concatinates.