topic Merge 2 tables with same field names in QlikView

Merge 2 tables with same field names

Mon, 06 Jul 2009 15:50:21 GMT

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

AW:Merge 2 tables with same field names

Mon, 06 Jul 2009 15:57:26 GMT

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

Merge 2 tables with same field names

Mon, 06 Jul 2009 16:09:29 GMT

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;

Merge 2 tables with same field names

Mon, 06 Jul 2009 16:16:20 GMT

Hi,

Try using exists() function in where clause.

- Sridhar

AW:Re: Merge 2 tables with same field names

Mon, 06 Jul 2009 16:18:32 GMT

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;

AW:Re: Merge 2 tables with same field names

Mon, 06 Jul 2009 16:28:37 GMT

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?

AW:Re: AW:Re: Merge 2 tables with same field names

Mon, 06 Jul 2009 16:38:14 GMT

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