topic synthetic key in QlikView

synthetic key

Tue, 21 Feb 2012 10:08:27 GMT

can we use qualify keyword when trying to avoid synthetic key situation in the files loaded from oracle data source.

if yes please explain with an example?

Re: synthetic key

jagan — Tue, 21 Feb 2012 10:10:27 GMT

Hi,

You can also avoid Synthetic key using the Qualify statement.

Example:

LOAD

FROM Table1;

LOAD

FROM Table2;

Qualify B;

In this way you can avoid the Synthetic key.

Regards,

Jagan.

synthetic key

Tue, 21 Feb 2012 10:21:34 GMT

SQL SELECT
ENAME &
SALARY AS KEY ,
    ENUM,
    MRGNAME
   FROM SCOTT.EMP123;

SQL SELECT
    ENAME&
     SALARY AS KEY ,
    ENO,
    DEPT
FROM SCOTT.EMPLOYEE;

this is my situation. tell me how to work on it?

synthetic key

jagan — Tue, 21 Feb 2012 10:26:06 GMT

Hi,

You won't get Synthetic key for this because you have only one column in column i.e., KEY

Table1:

SQL SELECT

ENAME &

SALARY AS KEY ,

ENUM,

MRGNAME

FROM SCOTT.EMP123;

Table2:

SQL SELECT

ENAME&

SALARY AS KEY ,

ENO,

DEPT

FROM SCOTT.EMPLOYEE;

Use the above script without Load.

Hope this helps you.

Regards,

Jagan.

synthetic key

Tue, 21 Feb 2012 10:30:12 GMT

its not working that is the problem i am facing right now.

synthetic key

CELAMBARASAN — Tue, 21 Feb 2012 10:33:48 GMT

Hi,

I think you should use +(Plus) instead of ampersand(&)

Table1:

SQL SELECT

ENAME +

SALARY AS KEY ,

ENUM,

MRGNAME

FROM SCOTT.EMP123;

Table2:

SQL SELECT

ENAME+

SALARY AS KEY ,

ENO,

DEPT

FROM SCOTT.EMPLOYEE;

Celambarasan

synthetic key

Tue, 21 Feb 2012 10:41:39 GMT

still its not working with the use of + sign.

the error i am getting is

SQL##f - SqlState: 22005, ErrorCode: 1722, ErrorMsg: [Oracle][ODBC][Ora]ORA-01722: invalid number

SQL SELECT
    ENAME +
     SALARY as key   ,
    ENO,
    DEPT
FROM SCOTT.EMPLOYEE

help me please

synthetic key

jagan — Tue, 21 Feb 2012 10:44:58 GMT

Hi,

I think you are using Oracle then

Table1:

SQL SELECT

ENAME || SALARY AS KEY ,

ENUM,

MRGNAME

FROM SCOTT.EMP123;

Table2:

SQL SELECT

ENAME || SALARY AS KEY ,

ENO,

DEPT

FROM SCOTT.EMPLOYEE;

Regards,

Jagan.

Re: synthetic key

CELAMBARASAN — Tue, 21 Feb 2012 10:45:36 GMT

Hi,

Oh sorry,

In oracle it is || (Concat symbol or pipe symbol) which is above the enter key.

Celambarasan

synthetic key

Tue, 21 Feb 2012 11:01:52 GMT

thnx for your help...its working.

but in my case i am concatenating name and salary. the resultant list box is showing fieldvalues like this

ram345657

jai657289

can we use qualify keyword in it ??

if yes please help

Re: synthetic key

CELAMBARASAN — Tue, 21 Feb 2012 11:04:58 GMT

Hi,

Why you need Qualify keyword here?I mean you have only one common field know then it won't create synthetic key.

Celambarasan

synthetic key

Tue, 21 Feb 2012 11:21:53 GMT

no i want to know can we use qualify keyword to tackle the problem of synthetic key??

i am not talking about using it now.

synthetic key

jagan — Tue, 21 Feb 2012 11:25:39 GMT

Hi,

You can also use Qualify for avoiding Synthetic Keys. Qualify just renames the Column name with TableName.ColumnName format. So, if we qualify the column then the name changes. In this way also we can avoid the Synthetic Key.

Hope this helps you.

Regards,

jagan.

synthetic key

CELAMBARASAN — Tue, 21 Feb 2012 11:29:24 GMT

Hi,

you should use Qualify before loading the table.

Ex:

Qualify Field1,Field2;

Table:

Load

Field1,

Field2,

Field3,

Field4

From..

Qualify makes the Field1 and Field2 field name as Table.Field1 and Table.Field2.

Celambarasan

synthetic key

Tue, 21 Feb 2012 11:34:20 GMT

will it work when we are using the files from oracle or any other database?

synthetic key

CELAMBARASAN — Tue, 21 Feb 2012 11:37:02 GMT

Hi,

Yes it will work.

Celambarasan