Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Another Join Question

I have a data set loaded - all relational as per the image below - all working as expected.

I have another data set that contains information about the quality of the overall data set.

Marked in RED are the Foreign Keys that will align with the BusinessRules tables PrimaryKey (marked in BLUE)

I am a little confused as to the right way to join the tables up ... From a script Perspective what should it look like?

Join after load? Join during load? And what should the code look like .. an example would be very useful if possible?

Keys.jpg

1 Solution

Accepted Solutions
datanibbler
Champion
Champion


Hi Robin,

you're welcome.

Splitting your script onto tabs is a very good idea. It will help others who have to maintain your apps - your colleague or/and eventually your follower - to understand.

=> Can be enhanced IMHO by using numbers - the tab_labels are then like "03_blabbery" and "04_bumblebee" - that way, for instance, you would know that the tab 04 necessarily has to be on the right of the tab 03 because it builds upon the latter (RESIDENT LOAD or so, I have those all over the place)

To your question:

- The fields on which you want to join have to have the same name - same proceeding as with QV's automatic
  association. => so it would be

// ?This is your primary table?

KeyTable:

LOAD MasterTable;

SQL SELECT MasterTable

FROM `C:\Users\RMiller\Desktop\QlikNext\Insurance.xlsx`.`KeyTable$`;

Agents:

LOAD `Agent ID` as [Agent Primary Key],

    Agents,

    Agent_EntityID as MasterTable;

SQL SELECT `Agent ID`,

    Agents,

    Agent_EntityID

FROM `C:\Users\RMiller\Desktop\QlikNext\Insurance.xlsx`.`Agents$`;

HTH

Best regards,

DataNibbler

View solution in original post

5 Replies
datanibbler
Champion
Champion

Hi,

the rules for joining are actually quite easy:

- The "primary" table (the one that you want to join another to) has to be loaded first.

-> then you type the keyword JOIN (write the "primary" table in brackets to make it explicit)

-> then comes the LOAD statement for the "secondary" table

You just have to be careful about the keys. As a rule - there are exceptions, of course - the "primary" table should have a unique key (where the "hover_quick_tip" says "perfect" or "primary")

HTH

Best regardes,

DataNibbler

Not applicable
Author

As an example could you comment on what this should be as a statement ( I tend to split each table load into separate tabs .. this is only two of the tables of many .. and will the join statement have any affect the already correct data model?

Currently looks like this:

KeyTable:

LOAD MasterTable;

SQL SELECT MasterTable

FROM `C:\Users\RMiller\Desktop\QlikNext\Insurance.xlsx`.`KeyTable$`;

Agents:

LOAD `Agent ID` as [Agent Primary Key],

    Agents,

    Agent_EntityID;

SQL SELECT `Agent ID`,

    Agents,

    Agent_EntityID

FROM `C:\Users\RMiller\Desktop\QlikNext\Insurance.xlsx`.`Agents$`;

The columns to join on are Mastertable and Agent_EntityID

And thanks by the way

DataNibbler

datanibbler
Champion
Champion


Hi Robin,

you're welcome.

Splitting your script onto tabs is a very good idea. It will help others who have to maintain your apps - your colleague or/and eventually your follower - to understand.

=> Can be enhanced IMHO by using numbers - the tab_labels are then like "03_blabbery" and "04_bumblebee" - that way, for instance, you would know that the tab 04 necessarily has to be on the right of the tab 03 because it builds upon the latter (RESIDENT LOAD or so, I have those all over the place)

To your question:

- The fields on which you want to join have to have the same name - same proceeding as with QV's automatic
  association. => so it would be

// ?This is your primary table?

KeyTable:

LOAD MasterTable;

SQL SELECT MasterTable

FROM `C:\Users\RMiller\Desktop\QlikNext\Insurance.xlsx`.`KeyTable$`;

Agents:

LOAD `Agent ID` as [Agent Primary Key],

    Agents,

    Agent_EntityID as MasterTable;

SQL SELECT `Agent ID`,

    Agents,

    Agent_EntityID

FROM `C:\Users\RMiller\Desktop\QlikNext\Insurance.xlsx`.`Agents$`;

HTH

Best regards,

DataNibbler

Not applicable
Author

I think the phrase I am looking for is "Wood for the Trees"

Thanks again ..

Robin

Not applicable
Author

Ahaaa ... now the load time has jumped to 5 minutes plus from 30 seconds .. seems to be creating lots of Synthetic keys ...

synth.jpg

Possibly because I have several tables that all need to join the same table ,, and naming the same column causes an issue ..

So back to Join ... ?