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: 
JustinDallas
Specialist III
Specialist III

Big Table A has multiple links to Bigger Table B

Hello Everyone,

I've to an issue where the scenario is as follows.

We have two main tables, Orders and Company(s).  The Order table looks like so

Order:

  ....

   %ShippingCompanyId

   %ConsigneeCompanyId

   %BillToCompanyId

   %SendingCompanyId

...

The issue is that I have 4 Companies attached to a single Order, which clearly will lead to a looping nightmare.  I've thought of doing something like the following pseudocode

Shippers:

LEFT KEEP(Order)

Qualify Shippers or something...

LOAD companyId as %ShippingCompanyId,

*

Resident Company

;

Consigners:

LEFT KEEP(Order)

Qualify Consigners or something...

LOAD companyId as %ConsigneeCompanyId,

*

Resident Company

;

That seems like it would work, but it doesn't pass the smell test and it looks like it could cause an eruption since I'm possibly QUADRUPLING the data needed for the company table.

What is the standard way to handle tables that can't be LEFT JOINned to another table (like a small State table (Name,Abbrev)) but have multiple linkings to another table?

Any help is greatly appreciated.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Add a "Role" field to the link table:

CompanyLink:

LOAD Distinct 'Shipper' as Role, OrderId, %ShippingCompanyId  as CompanyId Resident Order;

LOAD Distinct 'Consignee' as Role, OrderId,  %ConsigneeCompanyId as CompanyId Resident Order;

LOAD Distinct 'BillTo' as Role, OrderId,  %BillToCompanyId as CompanyId Resident Order;

LOAD Distinct 'SendingCompany' as Role, OrderId, %SendingCompanyId   as CompanyId Resident Order;


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

A standard approach would be to create a "role playing" link table.

CompanyLink:

LOAD Distinct OrderId, %ShippingCompanyId  as CompanyId Resident Order;

LOAD Distinct OrderId,  %ConsigneeCompanyId as CompanyId Resident Order;

LOAD Distinct OrderId,  %BillToCompanyId as CompanyId Resident Order;

LOAD Distinct OrderId, %SendingCompanyId   as CompanyId Resident Order;


This table will bridge Orders and Company:


Orders:OrderId-> CompanyLink:CompanyId-> Company


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

JustinDallas
Specialist III
Specialist III
Author

Thanks for the response Rob.  So I've got my link table created.  But is there a way for me to preserve the status of the "Role" in the UI and DataModel.  For example, when a user selects an Order, it will bring back 4 Companies, but how will I convey the role each company plays i.e it's the %BillToCompanyId versus the other companies.


Thanks for your help!!!!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Add a "Role" field to the link table:

CompanyLink:

LOAD Distinct 'Shipper' as Role, OrderId, %ShippingCompanyId  as CompanyId Resident Order;

LOAD Distinct 'Consignee' as Role, OrderId,  %ConsigneeCompanyId as CompanyId Resident Order;

LOAD Distinct 'BillTo' as Role, OrderId,  %BillToCompanyId as CompanyId Resident Order;

LOAD Distinct 'SendingCompany' as Role, OrderId, %SendingCompanyId   as CompanyId Resident Order;


-Rob

http://masterssummit.com

http://qlikviewcookbook.com