topic Issue with multiple CONCATENATE statements in script in QlikView

Issue with multiple CONCATENATE statements in script

Tue, 09 Apr 2013 01:50:36 GMT

To whom it may concern:

I am attempting to perform successive Concatenates to the Main Data table from the Customers Extended table and the Employees table respectively. The first one works successfully, but the second results in a script error. I don't believe there is anything wrong with my syntax as I don't get any errors in the Script Editor until the script is executed. Also, if I take out the middle first CONCATENATE statement, the second CONCATENATE statement (i.e., from the Employees table) executes properly. It is only when I try to run the whole statement that the second CONCATENATE statement fails.

Is there something that needs to occur in between CONCATENATE statements in order to append to the same table twice? I am hoping this is a relatively simple answer as I am new to the QlikView community.

Thank you in advance,

Jeff

Below is the script I am executing:

[Main Data]:

LOAD
    ID as [%Customer ID],
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company;

SQL SELECT *
FROM Customers;

CONCATENATE([Main Data])

LOAD
    ID as [%Customer ID],
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company,
    `File As`;

SQL SELECT *
FROM `Customers Extended`;

CONCATENATE([Main Data])

LOAD
    ID as [%Employee ID],
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company;

SQL SELECT *
FROM Employees;

Re: Issue with multiple CONCATENATE statements in script

sushil353 — Tue, 09 Apr 2013 04:53:39 GMT

Hi,

What is your error msg?

try to test this script:

[Main Data]:

LOAD
    ID as [%Customer ID],
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company;

SQL SELECT *
FROM Customers;

CONCATENATE([Main Data])

LOAD
    ID as [%Employee ID],
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company;

SQL SELECT *
FROM Employees;

CONCATENATE([Main Data])

LOAD
    ID as [%Customer ID],
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company,
    `File As`;

SQL SELECT *
FROM `Customers Extended`;

HTH

Sushil

Re: Issue with multiple CONCATENATE statements in script

jagan — Tue, 09 Apr 2013 11:53:59 GMT

Hi,

Is all the columns

     ID,
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company,
    `File As`

exists in Customers Extended table?

Can you tell what error you are getting?

Regards,

Jagan.

Re: Issue with multiple CONCATENATE statements in script

Anonymous — Tue, 09 Apr 2013 12:27:30 GMT

Jeff,

There is nothing wrong with the multiple CONCATENATE by itself. The only suspicious thing I notice here is the character used for the single quotation mark. It should be chr(39), as your text "don't", but what I see here is chr(96). On the other hand, if it works with one part of the script, it should work with another; maybe it is just a copy/paste conversion (?).

Additional notes:
Using table label with space is allowed, but I never use it. Prefer something like Main_Data.
Not a good idea to use SELECT * FROM... It is always safer to be explicit, it makes script clearer and easier to troubleshoot.

As for your problem, I recommend you to implement the advices above, and tell what the error is.

Regards,
Michael

Re: Issue with multiple CONCATENATE statements in script

Tue, 09 Apr 2013 14:17:36 GMT

Jagan,

No. There are several other columns in the Customer Extended Table which I have now included below, but I removed some of them so the code would fit cleanly in the discussion thread. I believe you have helped me uncover what the problem may be. I am connecting to the datasource using an OLE DB connection. Based on Sushil's suggestion, I rewrote the code to load the Customers, Employees, Shippers, and Suppliers information and those tables loaded fine (all of these tables contain the same columns with the exception of the primary key or ID values).

Note: I am using QlikView personal edition.

However, when I attemp to load the Customers Extended table to attempt to load it, I receive a pop-up that says:

QvConnect 11.20 SR1 has stopped working, and I am prompted to close the program.

If I close that dialog box, as opposed to closing the program, I receive a generic script error that says,

Script Error

SQL SELECT *

FROM 'Employees Extended'

CONCATENATE([Main Data])LOAD
    ID as [%Customer ID],
    Address,
    Attachments,
    `Business Phone`,
    City,
    Company,
    `Contact Name`,    - This field is not in the Customers main table
    `Country/Region`,
    `E-mail Address`,
    `Fax Number`,
    `File As`,         - This field is not in the Customers main table
    `First Name`,
    `Home Phone`,
    `Job Title`,
    `Last Name`,
    `Mobile Phone`,
    Notes,
    `State/Province`,
    `Web Page`,
    `ZIP/Postal Code`;

SQL SELECT *
FROM `Customers Extended`;

Re: Issue with multiple CONCATENATE statements in script

jagan — Wed, 10 Apr 2013 06:10:33 GMT

Hi,

Can you some sample data in Excel file of all the 3 tables, it helps in understanding the problem.

Regards,

Jagan.

Re: Issue with multiple CONCATENATE statements in script

Fri, 12 Apr 2013 17:19:21 GMT

Thank you Michael. I re-reviewed my code, and was going about this the wrong way, so I changed my script substantially. I appreciate your insight.