topic Re: how to avoid synthetic key without alias in QlikView

how to avoid synthetic key without alias

noviceneil — Fri, 09 Apr 2021 10:07:12 GMT

Hello Experts,

I need some help.

I have been trying to do the following,

TABLE 1:

LOAD ID, B,C, D, E from [ lib://A.qvd] (qvd)

TABLE 2:

LOAD ID, X, Y from [ lib://B.qvd] (qvd)

TABKE C:

LOAD ID, B, C, D RESIDENT TABLE 1;

left join

LOAD ID, X, Y RESIDENT TABLE 2;

DROP TABLE 1
DROP TABLE 2;

TABLE 3:

LOAD ID, B,C, D, E from [ lib://X.qvd] (qvd);

TABLE 4:

LOAD ID, X, Y from [ lib://Y.qvd] (qvd);

TABLE D :

LOAD ID, B, C, D RESIDENT TABLE 3;

left join

LOAD ID, X, Y RESIDENT TABLE 4;

DROP TABLE 3;
DROP TABLE 4;

Now at this point I have 2 tables, TABLE C and TABLE D, with similar column structure , i.e. ID, B, C, D, X, Y , where, as the column names matches , it is creating automatic association in QS by creating a synthetic key on all columns which is expected. But I want to restrict that and create a table by making the join based on column C and D only. is there any other ways than to renaming / creating aliases for all the columns other than C and D?

Re: how to avoid synthetic key without alias

rwunderlich — Fri, 09 Apr 2021 16:01:15 GMT

There is no way to define table associations other than implicitly by field name. So if you want separate tables and no synkey, you will have to change some names.

-Rob

Re: how to avoid synthetic key without alias

Stanley_ — Mon, 12 Apr 2021 09:56:12 GMT

Using QUALIFY. This enforces QlikView to qualify all/selected fields.
Autonumber. If you need to concat the Syn Key candidate fields and create your own key, you can use Autonumber/Autonumberhash128/Autonumberhash256 function.
Aliasing.

Re: how to avoid synthetic key without alias

PrashantSangle — Mon, 12 Apr 2021 10:36:13 GMT

Hi @noviceneil , all the solution is already suggested by Rob sir & Stanley. But I have curiosity that why you don't want to do aliasing field , any specific reason behind it.

Regards,

Re: how to avoid synthetic key without alias

noviceneil — Mon, 12 Apr 2021 14:03:38 GMT

Hi Prashant,

typical business use case...and also quite a number of columns I have in those tables. FOr later handling as well I didn't want to make alias.