Merge 2 tables with same field names

[#1]

Hi all,

The script below is working well, but what script do I need when 2 fields, in different tables, has the same name? For example: customers.description and orders.description. Both descriptions I need.

LOAD

CustomerID, OrderID, OrderDate, EmployeeID, ShipperID, CompanyName, Freight;

SQL

Select *

FROM

Customers, Orders, Shippers

WHERE

Customers.CustomerID=Orders.CustomerID

AND

Shippers.ShipperID=Orders.ShipperID;

Thanks in advance,

Regards,

Arjan IJlenhave

[#2 · Accepted solution]

1. You can rename the fields with "AS":

LOAD customers.description AS desc1,

orders.description AS desc2;

2. You can "qualify" all fields:

QUALIFY *;

LOAD description,*;

SELECT * FROM customers;

LOAD description,*;

SELECT * FROM orders;

Greetz

[#3]

Thanks, but now I got een error 'Field not found - <Customers.CustomerID>'

The script:

LOAD

Customers.CustomerID as CustIDCus,

Orders.CustomerID

as CustIDOrd,

OrderID

,

OrderDate

,

EmployeeID

,

ShipperID

,

CompanyName

,

Freight;

SQL

Select

*

FROM

Customers,

Orders,

Shippers

WHERE

Customers.CustomerID=Orders.CustomerID

AND

Shippers.ShipperID=Orders.ShipperID;

[#4]

Hi,

Try using exists() function in where clause.

- Sridhar

[#5]

Try joining the tables via QV script and not in SQL:

QUALIFY *;
UNQUALIFY '%*';

Customers:
LOAD CustomerID AS %CUSTOMERID%,
*;
SQL SELECT * FROM Customers;

Orders:
LOAD CustomerID AS %CUSTOMERID%,
ShipperID AS %SHIPPERID,
*;
SQL SELECT * FROM Orders;

Shippers:
LOAD ShipperID AS %SHIPPERID%,
*;
SQL SELECT * FROM Shippers;

[#6]

Ok, but I got een sql query with all the joins in it.

Is it possible to use a sql query in Qlikview or is it better to redesign it?

[#7]

Yes, it is possible to use complex SQL statements in QV script. You can use anything the ODBC driver does support.

And yes, it is better to keep the SQL script simple and to load only the base tables.

Otherwise you will load the tables <xx> times instead of only one time! 😉

But it depends...

Greetz