topic Synthetic key in QlikView

Synthetic key

SakshamRawat — Sat, 16 Nov 2024 19:47:43 GMT

I have a requirement of filtering two tables with two common fields. If I concatenate fields i wont get fields available to be put in filter pane separately. Suppose Table 1 has date,name,marks,DOB,birthmark,country and Table2 has date,country,address,zone and state as fields. date and country are to be put as common filters for both tables. Help is appreciated.

Re: Synthetic key

sumanta1234 — Tue, 29 Oct 2019 10:13:29 GMT

Hi,

TABLE1:
LOAD
date&'-'&country AS DATE_COUNTRY_KEY,
date,
name,
marks,
DOB,
birthmark,
country
FROM TABLE1;

TABLE2:
LOAD
date&'-'&country AS DATE_COUNTRY_KEY,
address,
zone,
state
FROM TABLE2;

Re: Synthetic key

SakshamRawat — Fri, 01 Nov 2019 06:39:27 GMT

Hi! First of all,Thank you for your efforts. Could you help me with filter pane too. If i make the composite key like you have demonstrated, I don't find any way how i can put both fields as common filter.

Re: Synthetic key

Brett_Bleess — Wed, 06 Nov 2019 14:25:06 GMT

Saksham, have a look at the following Design Blog posts, I think it may help explain things and potentially help you with getting what you want in your data model. There are hundreds of other posts in this area as well, so I will also include the base URL for you, so you can further search for other posts if you like.

In this case, I would think it may be better to join the tables into a single table, but I am sure you have reason for keeping them separate, more info on the use case may help on this one...

https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett