topic Re: Direct discovery: multiple tables with Cloudera ODBC driver for Impala in QlikView

Direct discovery: multiple tables with Cloudera ODBC driver for Impala

matteogm — Fri, 05 Sep 2014 16:28:27 GMT

Hi everybody,

I'm using Qlikview 11.2 SR7 and Cludera ODBC Driver for IMPALA v2.5.13.

How can I use the direct discovery to laod two tables with a common field (this field must be a DIMENSION as a foreign key that link the two table)??

EXAMPLE

TABLEA:

DIRECT QUERY
DIMENSION

Field_ID

MEASURE

measureA

from ImpalaTableA;

TABLEB:

DIRECT QUERY
DIMENSION

Field_ID

MEASURE

measureB

from ImpalaTableB;

I tried using JOIN and WHERE, as described in the document of direct discovery but the script gives me an error.

Do you have any suggestions?

Thanks a lot.

Matteo

Re: Direct discovery: multiple tables with Cloudera ODBC driver for Impala

JonnyPoole — Fri, 05 Sep 2014 17:40:43 GMT

I imagine it would be something like the following. Can you post the join syntax you have tried already ?

DIRECT QUERY
DIMENSION

Field_ID

MEASURE

measureA,

measureB

from ImpalaTableA, ImpalaTableB

where ImpalaTableA.Field_ID= ImpalaTableB.Field_ID;

Re: Direct discovery: multiple tables with Cloudera ODBC driver for Impala

matteogm — Mon, 08 Sep 2014 09:50:24 GMT

Thanks Jonathan,

I've solved. It was necessary to include alias and the table reference for the field Field_id, like this:

DIRECT QUERY
DIMENSION

ImpalaTableA.Field_ID as Field_ID //<----HERE

MEASURE

measureA,

measureB

from ImpalaTableA, ImpalaTableB

where ImpalaTableA.Field_ID= ImpalaTableB.Field_ID;

But now there is another problem, it seems that the join is not done properly. Even using the following code:

DIRECT QUERY
DIMENSION

ImpalaTableA.Field_ID as Field_ID

MEASURE

measureA,

measureB

from ImpalaTableA join ImpalaTableB

on ImpalaTableA.Field_ID= ImpalaTableB.Field_ID;

It is performed the Cartesian product of all rows, even if the two tables have the same Field_ID.

Do you have any suggestions?

Re: Direct discovery: multiple tables with Cloudera ODBC driver for Impala

matteogm — Thu, 11 Sep 2014 13:19:11 GMT

Now the join between the two tables is done properly. Joining two or more table, informations belonging to an original table are multiplied (repeating fields with the same value). Table box component that shows these fields can not unify equal row from the DIRECT DISCOVERY. This should be a known issue.