topic Using Left Join in one to many relation in App Development

Using Left Join in one to many relation

magnusrydberg — Tue, 07 Feb 2017 18:58:19 GMT

I have two files A and B that has a "one to many relation" and I'm using a left join.

I've got duplicate rows since B can contain several records for each A. B can also miss corresponding records for some A records thats why I want to do a Left join.

How should I write in the script to only join with the first record in B if there are more than one?

Code example today:

// Transactions

LOAD InvoiceType,

Status as InvoiceStatus,

Network as InvoiceNetwork,

TransportCompany as InvoiceCompany,

TransportFacility as InvoiceFacility,

CustomerCompany as InvoiceCustomerCompany,

CustomerFacility as InvoiceCustomerFacility,

CustomerAlias as InvoiceCustomerAlias,

LONumber as InvoiceOrderNumber,

OrderType as InvoiceOrderType;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.InvoiceTransaction where Network='NET1';

// Comments

Left Join

LOAD StringKey1 as InvoiceOrderNumber,

CommentString as InvoiceComment;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.Comment ;

Re: Using Left Join in one to many relation

swuehl — Tue, 07 Feb 2017 19:51:58 GMT

Use a MAPPING table and ApplyMap() function instead.

Don't join - use Applymap instead

If your mapping table shows multiple key - value pairs ([edit: with the same key!] like in your case), only the first pair will be considered by design.

edit: something along these lines

MAP:

MAPPING

LOAD StringKey1 as InvoiceOrderNumber,

CommentString as InvoiceComment;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.Comment ;

Code example today:

// Transactions

LOAD InvoiceType,

Status as InvoiceStatus,

Network as InvoiceNetwork,

TransportCompany as InvoiceCompany,

TransportFacility as InvoiceFacility,

CustomerCompany as InvoiceCustomerCompany,

CustomerFacility as InvoiceCustomerFacility,

CustomerAlias as InvoiceCustomerAlias,

ApplyMap('MAP', LONumber, 'no comment found') as InvoiceComment,

LONumber as InvoiceOrderNumber,

OrderType as InvoiceOrderType;

SQL SELECT * FROM "MyL_PROD_220".OTSDATA.InvoiceTransaction where Network='NET1';

Re: Using Left Join in one to many relation

magnusrydberg — Wed, 08 Feb 2017 08:03:33 GMT

Thanks a lot for your help

Now I see the benefits of using Mapping function instead of Join!

Thanks again

// Magnus

Re: Using Left Join in one to many relation

swuehl — Wed, 08 Feb 2017 08:06:56 GMT

You're welcome.

If your issue is resolved, then please close this thread by flagging correct and / or helpful answers.

Qlik Community Tip: Marking Replies as Correct or Helpful