Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alex59800
Contributor
Contributor

to Link 2 tables : one field on mutiple possible fields

Hello all,

I have a table with fields as follow

The first table is my main table, with customer ID, references, and different hierarchy levels.


Table A:

HIE1 HIE2 HIE3 HIE4 HIE5 CUSTOMER REFERENCE

   A     A1    A2    A3    A4     000100             003

   B     B1    B2    B3    B4     000101             004

And another table with only a single hierarchy field (my key with table A), department field and reference. My main problem, hierarchy field presented here could be possibly joined either with HIE1 or HIE2 or HIE3 on table A.

Table B :  

HIE  REFERENCE    DEPT

A1         003               GCY

B2         004               FRT

Result i'd like to have:

HIE1 HIE2 HIE3 HIE4 HIE5 CUSTOMER REFERENCE DEPT

   A     A1    A2    A3    A4     000100             003          GCY

   B     B1    B2    B3    B4     000101             004           FRT

I can not find how to easily make a link btw two tables with one field that possibly be in different column.

in Table B, the hierachy key not indicate the level of hierarchy present in table A.

Is anyone has a solution ? Would be very nice. Hope i was clear enough

1 Reply
Gysbert_Wassenaar

Try this:

mapTableB:

Mapping LOAD

     HIE & '|' &  REFERENCE as Key,

     DEPT

FROM

     [Table B]

TableA:

LOAD

     HIE1,

     HIE2,

     HIE3,

     HIE4,

     HIE5,

     CUSTOMER,

     REFERENCE,

     Applymap('mapTableB', HIE2& '|' &  REFERENCE ,

          Applymap('mapTableB', HIE3& '|' &  REFERENCE ,

               Applymap('mapTableB', HIE4& '|' &  REFERENCE ,

                    Applymap('mapTableB', HIE5& '|' &  REFERENCE , Null() )))) as DEPT

FROM

     [Table A]:


talk is cheap, supply exceeds demand