Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

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

Re: Big Table A has multiple links to Bigger Table B

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

3 Replies

Re: Big Table A has multiple links to Bigger Table B

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:


OrdersSmiley SurprisedrderId-> CompanyLink:CompanyId-> Company


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

JustinDallas
Valued Contributor II

Re: Big Table A has multiple links to Bigger Table B

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!!!!

Re: Big Table A has multiple links to Bigger Table B

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

Community Browser