topic Re: Qlik View - Data Association Question in QlikView

Qlik View - Data Association Question

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am brand new to Qlik View.

I am importing data from a SQL instance and the default associations created are incorrect. As far as I can tell, Qlik View creates associations between identically named fields in tables. The issue is, I have 40+ tables each using a field called "ID" as a PK so Qlik View is trying to create associations on this field (which is definitely not correct).

Is there a way I can clean this up using a tool like "Table Viewer" to redefine relationships, or, as I am beginning to fear, the only way to do this is to manually rename all sorts of fields in the import script? Really hoping there is a better way to do this!

Thanks in advance for your time and help.

Re: Qlik View - Data Association Question

Gysbert_Wassenaar — Wed, 18 Apr 2018 17:22:10 GMT

You could add this to your script before loading the data:

QUALIFY ID;

That will prefix every ID field with the name of the table it occurs in. You're then free to rename fields as needed to create the correct associations.

The other fields will not be prefixed. If you want to prefix all fields (generally not recommended) use QUALIFY *;

Re: Qlik View - Data Association Question

vvvvvvizard — Wed, 18 Apr 2018 17:24:50 GMT

Rename the existing fields in the script like this

Existing fieldname as new_fieldname ,

Re: Qlik View - Data Association Question

Anonymous — Wed, 18 Apr 2018 17:43:42 GMT

Thanks Gysbert...I appreciate the quick response.

Unfortunately, I guess I'm stuck renaming fields!

I suppose what I am struggling with is I can understand the need to manually define relationships if I was talking about disparate data sources (for example, trying to relate a SQL table to an Excel file). However, when pulling data in from a single relational database where all of the relationships are already correctly defined, it seems rather inefficient to have to essentially redefine those relationships because a bunch of fields happen to share the same name.

I should mention this gets further complicated because a number of the tables also use a field called "Description" so Qlik View has set up all kinds of synthetic associations.

I just don't see name matching as a good basis for establishing data relationships (especially within a single relational database)..Just my 2 cents though.

Thanks again for confirming.