Match the values from a list in different table.

[#1]

Hi All,

I have two tables in my scenario.

Table 1  is having all the Data

and Table 2 has only one field (or a List of Values)

Table1

Column 1	Column 2	Column 3	Column 4
1	4	7	ABC - A
2	3	4	ABC - B
6	6	2	ABC - C
3	6	7	PQR - JKL - A
23	3	5	PQR - JKL - B
54	3	2	ABC
23	7	9	PQR - JKL
6	3	8	XYZ - A
7	1	5	DEF - B

Table2

ColumnA
ABC
PQR - JKL
DEF

If any value in Table 2 ColumnA matches with any value of Table 1 Column4 ( excluding '- A', '- B' , '- C' ) then it should have value 1 else 0.

Expected Result :

Column 1	Column 2	Column 3	Column 4	Result
1	4	7	ABC - A	1
2	3	4	ABC - B	1
6	6	2	ABC - C	1
3	6	7	PQR - JKL - A	1
23	3	5	PQR - JKL - B	1
54	3	2	ABC	1
23	7	9	PQR - JKL	1
6	3	8	XYZ - A	0
7	1	5	DEF - B	1

Since these are two different tables direct if clause is not possible.

Pls help.

Thanks and Regards,

Anjali Gupta

[maxgro]

TableB:

Load Concat(ColumnA, '|') as Key_Match;

LOAD trim(subfield(ColumnA, '-')) as ColumnA

Inline [

ColumnA

ABC

PQR - JKL

DEF

];

LET v = Peek('Key_Match');

TableA:

LOAD *, -1*(Index('$(v)', NewColumn4)>0) as MatchFlag

WHERE NOT match(NewColumn4, 'A', 'B', 'C');

LOAD  *, trim(SubField(Column4, '-')) as NewColumn4

INLINE [

Column1, Column2, Column3, Column4

1, 4, 7, ABC - A

2, 3, 4, ABC - B

6, 6, 2, ABC - C

3, 6, 7, PQR - JKL - A

23, 3, 5, PQR - JKL - B

54, 3, 2, ABC

23, 7, 9, PQR - JKL

6, 3, 8, XYZ - A

7, 1, 5, DEF - B

];

if you want your original table you can add at the end of the script this

TableAA:

noconcatenate load Column1, Column2, Column3, Column4, max(MatchFlag) as MatchFlag

Resident TableA

group by Column1, Column2, Column3, Column4;

drop table A;

[trdandamudi]

Without sample data or app it is very difficult to suggest a solution. But give a try with the following code and see if it helps. I made few changes.... Hope this helps...

TableA:

LOAD  *,

subfield(Column4, ' ' ,1) as Key_Match

INLINE [

Column1, Column2, Column3, Column4

1, 4, 7, ABC - A

2, 3, 4, ABC - B

6, 6, 2, ABC - C

3, 6, 7, PQR - JKL - A

23, 3, 5, PQR - JKL - B

54, 3, 2, ABC

23, 7, 9, PQR - JKL

6, 3, 8, XYZ - A

7, 1, 5, DEF - B

];

TableB:

Load *,

subfield(ColumnA, ' ' ,1) as Key_Match

Inline [

ColumnA

ABC

PQR - JKL

DEF

];

Temp:

NoConcatenate

Load * Resident TableA;

Left Join (Temp)

Load * Resident TableB;

Drop Tables TableA,TableB;

Final:

NoConcatenate

Load Column1,

Column2,

Column3,

Column4,

Key_Match,

If(WildMatch(ColumnA,Left(Column4,3)&'*'),1,0) as Result

Resident Temp;

Drop Table Temp;

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

Table2:
LOAD * FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @2);

mapColA:
Mapping
LOAD ColumnA, '@ColumnAExists@'
Resident Table2;

Table1:
LOAD *,
    -(MapSubString('mapColA',[Column 4]) like '*@ColumnAExists@*') as Result
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[tamilarasu]

Hi Anjali,

One more solution from my side.

Table2:

LOAD Concat(Chr(39) & '*' & ColumnA & '*' ,',') as  ColumnA
FROM [https://community.qlik.com/thread/240758]
(html, codepage is 1252, embedded labels, table is @2) where Len(Trim(ColumnA))>0;

Let vMatch = Peek('ColumnA',0,'Table2');

DROP Table Table2;

Table1: 
LOAD *, 
If(WildMatch([Column 4],$(vMatch)),1,0) as Result 
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @1); 

Set vMatch =;

Result:

[#6]

Hi Thirumala,

This won;t work when I have value like

PQR - JKL - A

PQR - GHI - A

Where I have to match PQR - JKL and PQR - GHI seperately.

Thanks and Regards,

Anjali Gupta

[#7]

Hi Marco,

This works for me. Thanks a lot.

Could you pls brief in on '@ColumnAExists@'  , What are these values/ variables called?

And how these are used.

Thanks and Regards,

Anjali Gupta

[MarcoWedel]

Hi,

the string '@ColumnAExists@' in this case is just a marker to identify matching substrings.

It only has to be improbable to occur in your data but it could be any string. So there's no special meaning to this particular string I used. To get a random marker string you could also use something like this:

mapColA:
Mapping LOAD ColumnA, Hash128(1)
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @2);

Table1:
LOAD *, Sign(Index(MapSubString('mapColA',[Column 4]),Hash128(1))) as Result
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[#9]

Thanks Marco.

Regards,

Anjali Gupta