Probably simple data import query

[unknownb]

Hello,

I'm hopefully you can share some assistance as it's been a while since we setup our old instance.

We are pulling data in through Redshift.

We have a number of tables (10) which all have primary and secondary keys, however i'm having the issue on loading these in without loops.

Below is the basics, all tables come in with their own unique 'id' which are keys in other tables, for instance in the quote table we have 'id' which is the 'quoteid' in another table, the same with the board table for instance we have 'id' which is 'boardid' in other tables.

For some of the tables this is fine, however I am getting a lot of sym links and I'm honestly confused on how to solve this.

Below is just four tables, you'll see the main issue is the likes of formid, quoteit, boardid etc

[answer]:
LOAD
id as answerid,
questionid,
formid,
quoteid,
contactid,
createdat,
updatedat,
answer;

SELECT *
FROM dev.answer;

[board]:
Load
id as boardid,
userid,
archivestatus,
createdat as board.createdat,
createdby as board.createdby,
createdbyemail as board.createdbyemail,
name as board.name,
status as board.status,
updatedat as board.pdatedat,
updatedby as board.updatedby ,
visibility as board.visibility,
archievestatus as board.archievestatus,
receivedby as board.receivedby,
assigneduseremails as board.assigneduseremails;
SELECT *
FROM dev.board;

[contact]
load
id as contact.id,
userid,
formid,
quoteid,
boardid,
status as contact.status,
contactid;
SELECT *

FROM dev.contact;

[form]:
load
id as formid,
archivestatus as form.archivestatus,
createdat as form.createdat,
createdby as form.createdby,
createdbyemail as form.createdbyemail,
name as form.name,
status as form.status,
type as form.type,
updatedat as form.updatedat,
updatedby as form.updatedby,
visibility as form.visibility,
userid,
receivedby as form.receivedby,
assigneduseremails as form.assigneduseremails;

SELECT *

FROM dev.form;

[pedrobergo]

Hi @unknownb ,

Qlik uses the name of each field to create a relationship between the tables that you loading. It is called association.  Qlik also accept just one field to make these associations. If two tables has 2 fields with same name, it will create a synthetic key, wich means the compound key ($Syn 'n') with the 2 fields.

Seems that you created  many associations in your script with many field with many tables, so you will have many synthetic keys, and some circular references.

In your case, the tables has two or more fields with same name. Let's analyze it in pairs:

Table/Fields	answer	board	contact	form
answer			userid formid quoteid contactid	formid
board			contactid userid	userid
contact	userid formid quoteid contactid	contactid userid		formid userid
form	formid	userid	formid userid

To avoid this you need to:

• Rename the fields for all table. See Qualify and Unqualify statements
• Create a compound key to represent the relationship between the tables. Avoid use a field that you don't need to use. To create a compound key, just concatenate the keys using &'|'& 
  • Load   key1 &'|'& key2 AS NewKey
    (...)
• Choose one unique connection between the tables, avoiding circular references.
• Read the attachments, it is old but really good to understand it
• Try use Qlik Data Manager to load Redshift tables, it can solve most problems to you.

[],

Pedro