Joining tables with condition

[jindrichk]

Hello,

I'll show what I need to achieve on example:

Lets have two tables

Table1:

LOAD * INLINE [

          Table_Code, Table_Carrier

          'AA', 'E3'

          'BB', 'R7'

          'CC', 'F5'

];

Type:

LOAD * INLINE [

     Type_Code, Type_Carrier, Type_Data

     'AA', 'E3', 'AA E3'

     'AA', 'R7', 'AA R7'

     'AA', '', 'general AA'

     'BB', 'E3', 'BB E3'

     'BB', '', 'general BB'

];

I would like to list all rows from Table1 and join Type_Data from Type table to them. Rules are:

• Match the same Code (Table_Code with Type_Code)
• If there is row with the same Carrier in Type table as has the row in Table1, use this one. Otherwise use the one with blank Carrier if exists.

I have found this solution

J:

LOAD

          Table_Carrier as Carr,

          Table_Code as code

Resident

          Table1;

left join

LOAD

          Type_Carrier as Carr,

          Type_Code as code,

          Type_Data

Resident

          Type;

left join

LOAD

          Type_Code as code,

          Type_Data as Type_Data_G

Resident

          Type

WHERE

          IsNull(Type_Carrier) or Type_Carrier = '';

F:

NoConcatenate LOAD

          Carr,

          code,

          if(not IsNull(Type_Data), Type_Data, Type_Data_G) as Type_Data

Resident

          J;

But it doesn't seems 'elegant' to me. Isn't there any better way how to get to the right result? Using VB is probably a way but the user is accessing project via AJAX thin client and I'm not sure if the VB will work in this case.

Regards,

Jindra

[Gysbert_Wassenaar · Accepted solution]

TypeMap:

mapping

LOAD Type_Code&Type_Carrier as Key, Type_Data INLINE [

     Type_Code, Type_Carrier, Type_Data

     'AA', 'E3', 'AA E3'

     'AA', 'R7', 'AA R7'

     'AA', '', 'general AA'

     'BB', 'E3', 'BB E3'

     'BB', '', 'general BB'

];

Table1:

LOAD *, ApplyMap('TypeMap',Table_Code&Table_Carrier,ApplyMap('TypeMap',Table_Code,'No Value')) as Type_Data INLINE [

          Table_Code, Table_Carrier

          'AA', 'E3'

          'BB', 'R7'

          'CC', 'F5'

];

[jindrichk]

Thank you, that works perfectly