topic Joining 2 tables based on substring values of a field in QlikView

Joining 2 tables based on substring values of a field

Pulkit_Thukral — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Joining 2 tables based on substring values of a field

sunny_talwar — Tue, 13 Dec 2016 17:26:18 GMT

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?

Re: Joining 2 tables based on substring values of a field

vinieme12 — Tue, 13 Dec 2016 17:35:44 GMT

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

Re: Joining 2 tables based on substring values of a field

Pulkit_Thukral — Tue, 13 Dec 2016 18:07:52 GMT

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

Re: Joining 2 tables based on substring values of a field

vinieme12 — Tue, 13 Dec 2016 20:26:19 GMT

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

];

Re: Joining 2 tables based on substring values of a field

vinieme12 — Wed, 14 Dec 2016 21:23:18 GMT

if your Query is resolved please close the thread

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Joining 2 tables based on substring values of a field

varshavig12 — Thu, 15 Dec 2016 05:04:02 GMT

MapSubString is something new for me.

Thank you.

Re: Joining 2 tables based on substring values of a field

Anonymous — Thu, 15 Dec 2016 05:19:49 GMT

///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];

Re: Joining 2 tables based on substring values of a field

varshavig12 — Thu, 15 Dec 2016 05:25:28 GMT

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.