Using Left Join in one to many relation

[magnusrydberg]

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 ;

[swuehl]

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';

[magnusrydberg]

Thanks a lot for your help

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

Thanks again

// Magnus

[swuehl]

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