topic direct discovery issue in QlikView

direct discovery issue

alec1982 — Wed, 08 Jul 2015 21:03:12 GMT

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

Re: direct discovery issue

antoniotiman — Wed, 08 Jul 2015 21:12:19 GMT

Try

DIRECT QUERY

DIMENSION

User,

City,

Country

MEASURE

Sales,

Margin

from YourTable;

Re: direct discovery issue

alec1982 — Wed, 08 Jul 2015 21:24:36 GMT

Thank you! here is the actual query but still not working

SS:

DIRECT QUERY

DIMENSION

A.Id,

A.MainTableId,

A.Customer

MEASURE

A.Sales

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

Where A.Customer = 'ABC';

Re: direct discovery issue

antoniotiman — Wed, 08 Jul 2015 21:31:53 GMT

Which is Your QV version ?

Re: direct discovery issue

alec1982 — Thu, 09 Jul 2015 13:05:20 GMT

11.2 sr 10

Re: direct discovery issue

antoniotiman — Thu, 09 Jul 2015 13:28:24 GMT

Join Feature has been added to QV 11.20 SR 10 (see Release Notes).

But i didn't try this.

Regards,

Antonio

Re: direct discovery issue

Anonymous — Thu, 09 Jul 2015 13:43:55 GMT

Try get the data from SQL View and make join in View.

Re: direct discovery issue

alec1982 — Thu, 09 Jul 2015 14:12:44 GMT

not sure if I can do join within the view.. how can I do that? I clicked on select and able to make a select query from the sql view but that's about it..

Re: direct discovery issue

Anonymous — Thu, 09 Jul 2015 14:33:45 GMT

You will find all informations in this MS article

CREATE VIEW (Transact-SQL)

Re: direct discovery issue

alec1982 — Thu, 09 Jul 2015 18:00:05 GMT

thank you Dimitri but this is out of my control zone. any other solution?

Re: direct discovery issue

petter — Thu, 09 Jul 2015 18:21:53 GMT

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';

Re: direct discovery issue

alec1982 — Thu, 09 Jul 2015 18:33:42 GMT

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;

Re: direct discovery issue

petter — Thu, 09 Jul 2015 18:39:30 GMT

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?

Re: direct discovery issue

alec1982 — Thu, 09 Jul 2015 18:44:18 GMT

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

Re: direct discovery issue

antoniotiman — Thu, 09 Jul 2015 18:46:40 GMT

Try to use Native() like

Native('XYZ.Id') as Id

Re: direct discovery issue

petter — Thu, 09 Jul 2015 18:50:21 GMT

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

Re: direct discovery issue

petter — Thu, 09 Jul 2015 18:52:48 GMT

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

Re: direct discovery issue

alec1982 — Thu, 09 Jul 2015 18:53:12 GMT

this the query that gives the error.

Re: direct discovery issue

petter — Thu, 09 Jul 2015 19:01:29 GMT

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

Re: direct discovery issue

alec1982 — Thu, 09 Jul 2015 19:04:46 GMT

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;