Re: Concatenate Multiple Tables in Script - Qlik Community

Report Inappropriate Content · ‎2012-10-05

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.

jonathandienst · ‎2012-10-05

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Report Inappropriate Content · ‎2012-10-05

The table StaffRecord just has the first set of data from the first spreadsheet, and the StaffRecord-1 has the data from the remaining spreadsheets.

I have a Qualify statement (QUALIFY *;) at the start of my script, which is probably causing this but I should still be able to use one table, right?

alexpanjhc · ‎2012-10-05

try to take all the StaffRecord: out of your script except the first one.

That should solve the problem.

and make sure you do not have others have the same name.

Report Inappropriate Content · ‎2012-10-08

If I do that, I end up with various tables as the data sources themselves are not called "StaffRecord", so I get StaffRecord containing the rows from the first load, and the remaining tables are called whatever they are named on the spreadsheet.

This seems like it should be easy to do, but I've already spent hours trying to get it to work. All I want to do is load 5 tables with exactly the same headers into one table!

Report Inappropriate Content · ‎2012-10-08

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'.

Report Inappropriate Content · ‎2012-10-08

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.

Report Inappropriate Content · ‎2012-10-08

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);