topic Re: Need help on synthetic key in QlikView

Need help on synthetic key

agni_gold — Tue, 02 Jun 2015 20:24:41 GMT

I have 2 Tables , having large number of columns , i am attaching the sample tables here. In both table you can see there are some columns are common , but these tables are not liked together because there is no relation . but i need to put in same data model .

But common columns creates the synthetic key . I want to sort the both amounts on the basis of brand , product , customer and year , month .

We can not create the composite key also , because i need to give filter listbox in my dashboard . please help how to link without synthetic key .

Re: Need help on synthetic key

swuehl — Tue, 02 Jun 2015 20:26:43 GMT

Have you considered concatenating your two tables?

Re: Need help on synthetic key

agni_gold — Tue, 02 Jun 2015 20:30:34 GMT

no these are individual tables , we can not concatenate

Re: Need help on synthetic key

swuehl — Tue, 02 Jun 2015 20:39:15 GMT

Not sure if I understand, there is no relation between the table, you want them isolated?

Maybe consider using

QUALIFY *;

Table1:

LOAD * FROM Table1;

Table2:

LOAD * FROM Table2;

UNQUALIFY *;

Re: Need help on synthetic key

MarcoWedel — Tue, 02 Jun 2015 20:44:03 GMT

please post some sample data and your expected result.

thanks

regards

Marco

Re: Need help on synthetic key

arsal_90 — Tue, 02 Jun 2015 20:46:05 GMT

Create Link Table for Common dimensions. Hopefully it will resolved your problem.

Re: Need help on synthetic key

agni_gold — Tue, 02 Jun 2015 20:47:03 GMT

yes i have tried this also , but the problem is that when i want to provide the filter for Brand, Product and Customer , both the data will not be sort.

Re: Need help on synthetic key

agni_gold — Tue, 02 Jun 2015 20:48:03 GMT

for link table i have to add all the columns because there is no primary key in both the tables.

Re: Need help on synthetic key

swuehl — Tue, 02 Jun 2015 20:52:55 GMT

Maybe you can use a kind of trigger to copy selections in the field of interest around the two tables, to allow the user to select in one list box and filter both tables.

Re: Need help on synthetic key

agni_gold — Tue, 02 Jun 2015 20:57:10 GMT

Can you please explain little bit more about this ?

It seems to good for my question..

Re: Need help on synthetic key

swuehl — Tue, 02 Jun 2015 21:03:53 GMT

Something like:

Qualify both tables, so don't have a link between them.

Then create a field event trigger (in settings - document properties - triggers) OnSelect for the e.g. Sales.Brand field and create a Select - Select in field action for PL.Brand field.

Re: Need help on synthetic key

satishqlik — Tue, 02 Jun 2015 21:20:54 GMT

Use qualify and unqualify statements to single table(either table1 or table2)

QUALIFY *;

Table1:

LOAD *

FROM <path>;

UNQUALIFY *;

Table2:

LOAD *

FROM <path>;

Orelse by create link table

try like this

sales:

Load

invoice,

sales,

brand&'_'&customer&'_'&product&'_'&month&'_'&year as saleskey

from <path>;

pl:

Load

account No,

value

brand&'_'&customer&'_'&product&'_'&month&'_'&year as plkey

from<path>;

linktable:

Load

saleskey,

Null as plkey

resident sales;

linktable:

Load

plkey,

Null() as saleskey

resident pl;

Then it will concatenate automatically and creates 1 link table between the 2tables.

Hope this helps,

Re: Need help on synthetic key

pratap6699 — Wed, 03 Jun 2015 05:41:15 GMT

yes link table using with composite key is the best for this requiremnent

Re: Need help on synthetic key

ankit777 — Wed, 03 Jun 2015 06:42:30 GMT

Hi Agni,

You can load both tables. Just rename the second table common columns.

In the front end show only table 1 columns.

Now add trigger on each of the table 1 common column so that whenever anything is selected in table 1, same should be selected in table 2.