direct discovery issue

[alec1982]

Hi all,

I am trying to use direct discovery to load data from SQL but it is not working for me.

Currently, the script is as follow

TableA:

SQL Select

User,

City,

Country,

Sales,

Margin from table xyz;

working fine but when I add DIRECT SELECT I get error..

TableA:

DIRECT SELECT

User,

City,

Country,

Sales,

Margin from table xyz;

Any idea?

Best,

Alec

[petter]

The documentation on DIRECT QUERY specifically states:

Note:
It is not possible to refer to tables by alias in the FROM clause, you need to use the full table name.

So if you change your query to (it might work):

SS:

DIRECT QUERY

DIMENSION

  XYZTable.Id,

  XYZTable.MainTableId,

  XYZTable.Customer

MEASURE

  XYZTable.Sales

FROM XYZTable INNER JOIN Table2  on XYZTable.MainTableId = Table2.Id

    Where XYZTable.Customer = 'ABC';

[alec1982]

I took off the join and trying to get a simple table to work but I get the following error

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name 'XYZ.Id'. the column exist and works fine when loading it as a simple SQL query

SS:

DIRECT QUERY

DIMENSION

XYZ.Id,

XYZ.Salesman

MEASURE

XYZ.Sales

FROM XYZ;

[petter]

So what is your actual query that does not work? It seems like these XYZ table and Table2 are just mock-up table names... do you have an actual full query to share with us?

[alec1982]

it is the same exact structure..

Here is the query

SS:

DIRECT QUERY

DIMENSION

PQIBenchMarkTable.Id,

PQIBenchMarkTable.PQIMainTableId,

PQIBenchMarkTable.BenchMark

MEASURE

PQIBenchMarkTable.BenchMarkValue

FROM PQIBenchMarkTable

[antoniotiman]

Try to use Native() like

Native('XYZ.Id') as Id

[petter]

This is the query that works right? Could you share the one that doesn't work too?

[petter]

If you don't do a JOIN you don't need to prefix each column with a table qualifier...

[alec1982]

this the query that gives the error.

[petter]

Drop the table prefix then ... maybe QlikView is picky about it when you don't do a JOIN ....

[alec1982]

thank you guys, this query works. but I cannot find the measure column on the UI. when trying to select fields from table sS

sS:

DIRECT QUERY

DIMENSION

Native('PQIBenchMarkTable.Id') as ID,

Native('PQIBenchMarkTable.PQIMainTableId') as PQIMainTableId,

Native('PQIBenchMarkTable.BenchMark') as BenchMark

MEASURE

Native('PQIBenchMarkTable.BenchMarkValue') as BenchMarkValue

FROM PQIBenchMarkTable;