idea Re: Allow Compose to add a relationship with a different column(s) other than the PK in Suggest an Idea

Allow Compose to add a relationship with a different column(s) other than the PK

JorgeUriarte — Thu, 24 Sep 2020 18:34:23 GMT

In Compose when we add a relationship you can ONLY use the PK of the other table to do the lookup.

But there are several occasions where the lookup needs to be accomplished with another column, or columns, which are also unique. But we dont have any direct way to use that other than using a "lookup" functionality which doesnt perform well.

So adding something like an alternate unique key or something along those lines would help.

Re: Allow Compose to add a relationship with a different column(s) other than the PK

TimGarrod — Thu, 24 Sep 2020 20:01:50 GMT

Agree with this. Its common especially when conforming data between 2 sources to have to join on columns that are not the primary key / natural key.

Would be good for these "relationships" to be non-identifying (i.e. they don't impact missing references)

and even provide additional logic (e.g filtering logic on any join condition).

Re: Allow Compose to add a relationship with a different column(s) other than the PK

DWH2Go — Thu, 01 Oct 2020 16:16:22 GMT

Would you describe this as adding the ability to do lookups in the mart? If so we have also raised this.

Re: Allow Compose to add a relationship with a different column(s) other than the PK

JorgeUriarte — Thu, 01 Oct 2020 16:32:57 GMT

No, this is in the vault. The ability to load the relationship using something other than the PK

Re: Allow Compose to add a relationship with a different column(s) other than the PK

Marcoimp — Fri, 02 Oct 2020 16:50:00 GMT

Hi Ezh,

I do not agree! You want to destroy Qlik's main perfect feature: association!
I don't want to be a "purist" 😁, but I invite you to read this beautiful article if you don't want to use the Lookup() function.
https://qlikviewcookbook.com/2020/03/creating-temporary-script-associations/

😅

Re: Allow Compose to add a relationship with a different column(s) other than the PK

TimGarrod — Fri, 02 Oct 2020 16:58:50 GMT

Hi @Marcoimp - this is related to building out a data warehouse using Qlik Data Integration's data warehouse automation features (Qlik Compose for Data Warehouse) - not the association engine / features in QlikSense/QlikView...

Regardless its common for data to be linked/ joined via attributes that are not primary keys - but unique keys and this scenario is often found in source relational databases.

Re: Allow Compose to add a relationship with a different column(s) other than the PK

JorgeUriarte — Fri, 02 Oct 2020 17:43:10 GMT

As Tim mentioned. This has nothing to do with a BI app. As the original post mentioned, this is for Compose for Datawarehouses, which is Qliks app for datawarehouse automation.

Which currently does not support using anything other than the PK to create the join in the mapping load of a relationship between 2 business entities in the Data Vault created and maintained by Qlik Compose for Datawarehouses. And we need another way of doing this.

Re: Allow Compose to add a relationship with a different column(s) other than the PK

Rmierzwinski — Fri, 02 Oct 2020 18:23:43 GMT

Running into very similar issue with POC prospect, all PK's defined in the source DDL are based on generated UID's. Additional UNIQUE constraints are defined on natural keys used in generating the UID's.

Subsequent FK DEFINITIONS ARE defined on the UNIQUE (natural key) columns which prevents from creating Relationship in C4DW. In this particular case the FK's using Natural Key only extends to a few tables and the UNIQUE constraint enables us to use that Natural Key as the primary key for this POC.

Had there been Fks defined using both UID and Natural key(unique constraint) the workaround would have been more 'detailed'.

Re: Allow Compose to add a relationship with a different column(s) other than the PK - Status changed to: Open - Collecting Feedback

Tzachi_Nissim — Wed, 28 Oct 2020 07:13:16 GMT

Hi Jorge,

Thank you for your suggestion. Let me see if I got this right (correct me if I'm wrong please):

You are suggesting a new relationship type which will be part of the model. Instead of using the regular PK target, it will use a different set of fields in the target which are also unique. For example, use "Customer SSN" instead of "Customer ID".
I assume the target unique key must be:
1. Type 1
2. Not updatable.
The different target key would result in different code for looking up the surrogate key in loading the DWH, but from that point on the relationship will work the same as regular relationships (for data marts).

Does the above correctly capture the suggestion? It makes sense to me, but I am wondering if we need to enforce the uniqueness and the non-updatability of this target. An interesting consideration here is also if the relationship target should be ad-hoc, or have these "alternate keys" as part of the target definition.

Regards,

Tzachi

Re: Allow Compose to add a relationship with a different column(s) other than the PK

JorgeUriarte — Thu, 02 Dec 2021 16:52:28 GMT

and now I got VF also needing this feature. Actually I keep having customers needing this, especially when the transactions they want to add to the Data Vault come from different systems. Like VF, which they have Online and Offline applications for sales.

Re: Allow Compose to add a relationship with a different column(s) other than the PK

JorgeUriarte — Thu, 09 Dec 2021 15:24:11 GMT

and Perkin elmer needs to do this

Re: Allow Compose to add a relationship with a different column(s) other than the PK

JorgeUriarte — Thu, 09 Dec 2021 15:24:49 GMT

and Findorff also has this need

Re: Allow Compose to add a relationship with a different column(s) other than the PK - Status changed to: Closed - Archived

TimGarrod — Wed, 29 Mar 2023 21:04:07 GMT

Archiving due to age of idea and relevance (# of likes by community members).

This is under consideration for QLik Cloud Data Integration.