topic Concatenate Multiple Tables in Script in QlikView

Concatenate Multiple Tables in Script

Fri, 05 Oct 2012 14:23:45 GMT

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

jonathandienst — Fri, 05 Oct 2012 14:36:24 GMT

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

Fri, 05 Oct 2012 14:43:04 GMT

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?

Re: Concatenate Multiple Tables in Script

alexpanjhc — Fri, 05 Oct 2012 16:29:02 GMT

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.

Re: Concatenate Multiple Tables in Script

Mon, 08 Oct 2012 09:05:02 GMT

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!

Re: Concatenate Multiple Tables in Script

Mon, 08 Oct 2012 10:18:26 GMT

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

Mon, 08 Oct 2012 10:37:32 GMT

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

Mon, 08 Oct 2012 10:55:19 GMT

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