topic Re: Two table query doesn't work in QlikView

Two table query doesn't work

Wed, 24 May 2017 08:21:49 GMT

Hi everyone,

I'm trying to perform a query with two tables because i want to load a single table with more info.

I've theese two tables, already loaded in qlik from a db file:

Orders	Order Details
OrderID	OrderID
OrderDate	ProductID
ShipCity	Discount
	Quantity

In script tag, I've done this:

[OrdersD]:
LOAD 
  [OrderID],
  [OrderDate],
  [ShipCity],
    [ProductID],
    [Discount],
    [Quantity];
SQL SELECT `Orders`.`OrderID`
  `Orders`.`OrderDate`,
  `Orders`.`ShipCity`,
  `Order Details`.`ProductID`,
    `Order Details`.`Discount`,
    `Order Details`.`Quantity`
FROM `Orders`
WHERE `Orders`.`OrderID` = `Order Details`.`OrderID`;

But when I perform debugger, it's only says "Connector reply error: ***"

What I've done wrong?

Thank you.

Re: Two table query doesn't work

micheledenardi — Wed, 24 May 2017 15:12:12 GMT

I think you have to add "Order Details" in the "From" section...

[OrdersD]:

LOAD

[OrderID],

[OrderDate],

[ShipCity],

[ProductID],

[Discount],

[Quantity];

SQL SELECT `Orders`.`OrderID`

`Orders`.`OrderDate`,

`Orders`.`ShipCity`,

`Order Details`.`ProductID`,

`Order Details`.`Discount`,

`Order Details`.`Quantity`

FROM `Orders` , `Order Details`

WHERE `Orders`.`OrderID` = `Order Details`.`OrderID`;

Re: Two table query doesn't work

Wed, 24 May 2017 15:21:01 GMT

I've done wrong to copy my script, but there was Order Details table.

Re: Two table query doesn't work

micheledenardi — Wed, 24 May 2017 15:34:33 GMT

Try to split your select:

OrderHeader:

SQL SELECT

`Orders`.`OrderID`,

`Orders`.`OrderDate`,

`Orders`.`ShipCity`

FROM `Orders`;

left join(OrderHeader)

OrderItems:

SQL SELECT

`Order Details`.`OrderID`,

`Order Details`.`ProductID`,

`Order Details`.`Discount`,

`Order Details`.`Quantity`

FROM `Order Details`;

Does it work ?

Re: Two table query doesn't work

Wed, 24 May 2017 15:38:55 GMT

no, it doesn't work like that.

but where are sql queries?

Re: Two table query doesn't work

vishsaggi — Wed, 24 May 2017 15:39:23 GMT

May be this?

[OrdersD]:

LOAD

[OrderID],

[OrderDate],

[ShipCity],

[ProductID],

[Discount],

[Quantity];

SQL SELECT `Orders`.`OrderID`

`Orders`.`OrderDate`,

`Orders`.`ShipCity`,

`Order Details`.`ProductID`,

`Order Details`.`Discount`,

`Order Details`.`Quantity`

FROM `Orders`,

LEFT JOIN

`Order Details`

ON `Orders`.`OrderID` = `Order Details`.`OrderID`;

Re: Two table query doesn't work

Wed, 24 May 2017 15:43:28 GMT

nope!

I've tried to put that code into a single section, after the ones that upload table from my db file, but doesn't work.

When i perform debugger it stops at SQL SELECT ... row.

Re: Two table query doesn't work

micheledenardi — Wed, 24 May 2017 15:47:11 GMT

try to remove all the apex `

Re: Two table query doesn't work

Wed, 24 May 2017 15:49:58 GMT

nothing change

Re: Two table query doesn't work

vishsaggi — Wed, 24 May 2017 16:02:00 GMT

I am sure you might be, just wondering to know if you are connecting to the database before loading using connection string.. Are you getting any results when you run only your SQL script on SQL editor?

Re: Two table query doesn't work

Thu, 25 May 2017 07:32:43 GMT

i can't understand your question: I've created a OLE DB connection with a visual foxpro db, that had that two tables.

When i load single table with qlik, it works and script generated is lke this:

[Orders]:

LOAD

[CustomerID],

[EmployeeID],

[Freight],

[OrderDate],

[OrderID],

[RequiredDate],

[ShipAddress],

[ShipCity],

SQL SELECT `CustomerID`,

`EmployeeID`,

`Freight`,

`OrderDate`,

`OrderID`,

`RequiredDate`,

`ShipAddress`,

`ShipCity`,

FROM `Orders`;

[Order Details]:

LOAD

[Discount],

[OrderID],

[ProductID],

[Quantity],

[UnitPrice] AS [Order Details.UnitPrice];

SQL SELECT `Discount`,

`OrderID`,

`ProductID`,

`Quantity`,

`UnitPrice`

FROM `Order Details`;

But if i want to load my query as a new table, doesn't work!

Re: Two table query doesn't work

Thu, 25 May 2017 07:53:12 GMT

ok,

i fell very stupid: i notice just now that i've forgot a comma after

SQL SELECT `Orders`.`OrderID`

now it works!

sorry