Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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.

9 Replies
MVP
MVP

Re: Concatenate Multiple Tables in Script

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

Not applicable

Re: Concatenate Multiple Tables in Script

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 *Smiley Wink at the start of my script, which is probably causing this but I should still be able to use one table, right?

alexpanjhc
Valued Contributor

Re: Concatenate Multiple Tables in Script

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.

Not applicable

Re: Concatenate Multiple Tables in Script

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!

Not applicable

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

Not applicable

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.

Not applicable

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;

Not applicable

Re: Concatenate Multiple Tables in Script

Well, glad it worked out. But since you asked about creating empty tables (which I think is more 'correct' solution), you can check out this: http://www.qlikviewaddict.com/2012/03/creating-blank-tables.html

It also shows how to import into table using multiple Excel names like you are using (excel1, excel2, excel3... etc.)

Hope it helps.

Regards,

Gunnar

preminqlik
Valued Contributor II

Re: Concatenate Multiple Tables in Script

Having  same fields in all tables  then qlikview automatically concatinates.

Community Browser