Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jindrichk
New Contributor II

Joining tables with condition

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

Tags (3)
1 Solution

Accepted Solutions

Re: Joining tables with condition

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'

];


talk is cheap, supply exceeds demand
2 Replies

Re: Joining tables with condition

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'

];


talk is cheap, supply exceeds demand
jindrichk
New Contributor II

Re: Joining tables with condition

Thank you, that works perfectly

Community Browser