topic Re: Another Join Question in QlikView

Another Join Question

Fri, 11 Apr 2014 08:49:29 GMT

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?

Re: Another Join Question

datanibbler — Fri, 11 Apr 2014 08:59:38 GMT

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

Re: Another Join Question

Fri, 11 Apr 2014 09:07:30 GMT

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

Re: Re: Another Join Question

datanibbler — Fri, 11 Apr 2014 09:19:09 GMT

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

Re: Another Join Question

Fri, 11 Apr 2014 09:24:48 GMT

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

Thanks again ..

Robin

Re: Another Join Question

Fri, 11 Apr 2014 09:35:21 GMT

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 ... ?