Joining 2 tables based on substring values of a field

[Pulkit_Thukral]

Hi ,How to left join 2 tables in the below scenario:

Table A

Field Present-Node

Table B

Field Present-Brief Description

I want to have all rows of Table A  and Common rows from Table B(Left Join).

Now,How can the join be made,if Node field values are present as a sub string in Brief Description Column.

Ex:

Table A-

Node

abc

def

Table B-

Brief Description               Time

123abc11231                    10 Nov

31231231def412123          11 Nov

Output:

Table A-

Node     Brief Description             Time

abc         123abc11231                 10 Nov

def          31231231def412123        11 Nov

[sunny_talwar]

Match on text in Brief Description? Meaning everything else will be numbers of could it be like this

123abc11231xyz? and match needs to be on abc only?

[vinieme12]

Have you tried figuring out what are the prefix and suffixes?

[Pulkit_Thukral]

No,not necessarily numbers. Could be anything. just the exact string from Node field has to be present in Brief desc field.

[vinieme12 · Accepted solution]

The below Works

TableA:

LOad * inline [

Node

def

abc

];

mapA: 

Mapping 

LOAD *,

    '@-'&Node&'-@' 

Resident TableA;

Drop table TableA;

OUTPUT: 

LOAD *, TextBetween(MapSubString('mapA',BriefDescription),'@-','-@')  as Node

inline [

BriefDescription,Time

123abc11231,10 Nov

31231231def412123,11 Nov

];

[vinieme12]

if your Query is resolved please close the thread

Qlik Community Tip: Marking Replies as Correct or Helpful

[varshavig12]

MapSubString is something new for me.

Thank you.

[Anonymous]

///Can USe PurgeChar:

/// purgeChar([Brief Description],'1,2,3,4,5,6,7,8,9,0') as Node,

[Table1]:

load * Inline

[ Node

  abc

  def

  ];

[Table B]:

load

purgeChar([Brief Description],'1,2,3,4,5,6,7,8,9,0') as Node,

[Brief Description],

Time;

LOAD * Inline

[

Brief Description  ,  Time

123abc11231         , 10 Nov

31231231def412123    ,11 Nov

];

drop Table [Table1];

[varshavig12]

Hi Apoorva,

We cannot use purgeChar because as mentioned, the brief description can be combination of numbers and characters + Node.

Else this would have been the best option.