Joining tables with condition

jindrichk — Wed, 12 Jun 2013 07:19:55 GMT

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

Re: Joining tables with condition

Gysbert_Wassenaar — Wed, 12 Jun 2013 10:23:20 GMT

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'

];

Re: Joining tables with condition

jindrichk — Wed, 12 Jun 2013 10:49:18 GMT

Thank you, that works perfectly

topic Joining tables with condition in QlikView

Joining tables with condition

Re: Joining tables with condition

Re: Joining tables with condition