Data Loading - Linking two matching fields in data model

cgT — Wed, 07 Jun 2023 14:21:10 GMT

Hi there,

I'm currently trying to load in an .xlsx file via the load script (I've starred out my email address in the OneDrive link and server address):

LOAD
ShowName,
SetupPrcCode,
SetupNominalPrice,
SetUpCapacity,
SeaterName,
VscID as SetupVscID,
PriceincLev
FROM [lib://OneDrive - ******.*****@*********.co***_1 (**********)/QlikFiles/SetupPriceCaps.xlsx]
(ooxml, embedded labels, table is NC);

I already have a field called 'VscID' in my data model. I need it to link to the same 'VscID' value in the 'NC' table. The association is already made by 'ShowName' but it also needs to be able to link via 'VscID'. In order to prevent lopping etc I've renamed 'VscID' in the 'NC' table as 'SetupVscID'.

I have a feeling I need to create a primary key connecting the two VscID fields, am I correct?

Thanks for your help.

Re: Data Loading - Linking two matching fields in data model

Jason_Dyer — Mon, 19 Jun 2023 21:04:22 GMT

Hi CgT,

You have a handful of options to consider when it comes to joining tables with 2 or more matching key fields.

Field Level Concatenated/Compound Key: This is a simple solution where you create a new key in each table that combines your two fields into a single key value that can resolve synthetic keys or circular references. This approach doesn't always fulfill analytical requirements as it might not quite meet your needs from a data association standpoint. In certain situations, they are a useful tool for resolving data modeling challenges. If you go this route, I suggest through testing to make sure your selections and calculations come out as expected.

Table level JOIN: You could simply join (e.g. LEFT or OUTER) one table to the other to create a single table that contains the data you need. This tends to be the solution that offers the best performance for fact tables. Suggested reading on this topic: https://community.qlik.com/t5/Design/To-Join-or-not-to-Join/ba-p/1463102

A Link Table is a table that contains common fields from two or more tables, facilitating the connection between those tables for analytical purposes. These can be great for complex join scenarios, but can also add a level of complexity that you aren't necessarily looking for. I've personally used them on several apps, but it isn't usually my first choice (I tend to start with table JOINS or compound keys). This article might give you some further insight: https://www.ometis.co.uk/concatenate-link-tables-qlik/

This blog post does a good comparison between a link table and table concatenation (join): https://community.qlik.com/t5/Design/Concatenate-vs-Link-Table/ba-p/1467569

You mentioned looping, which I'm thinking refers to a circular reference. This article might help further unpack options: https://community.qlik.com/t5/Design/Circular-References/ba-p/1469332

Hopefully those articles will get you to where you need to be.

topic Re: Data Loading - Linking two matching fields in data model in Integration, Extension & APIs

Data Loading - Linking two matching fields in data model

Re: Data Loading - Linking two matching fields in data model