Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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
I think the phrase I am looking for is "Wood for the Trees"
Thanks again ..
Robin
Ahaaa ... now the load time has jumped to 5 minutes plus from 30 seconds .. seems to be creating lots of Synthetic keys ...
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 ... ?