topic Re: Match the values from a list in different table. in QlikView

Match the values from a list in different table.

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Match the values from a list in different table.

lironbaram — Wed, 23 Nov 2016 11:04:04 GMT

the easiest way will be to connect the 2 tables via the join field

so in the load script change the name of Column A in table 2 Column 4

then a direct if will work

Re: Match the values from a list in different table.

Wed, 23 Nov 2016 11:10:01 GMT

Hi,

Thanks for the quick response but this doesn't help.

The values in Column4 and ColumnA are different and doesn't have an exact match. I have to match the substring from Column4 to ColumnA. Also, if I just rename the ColumnA to Column4, i won't be able to access the ColumnA values in the Script itself.

Reagrds,

Anjali Gupta

Re: Match the values from a list in different table.

Anonymous — Wed, 23 Nov 2016 11:20:18 GMT

An idea

Re: Match the values from a list in different table.

Wed, 23 Nov 2016 11:40:21 GMT

Hi Marcio,

Thanks for the response. But I am looking forward to do the same on the Script Level and not UI.

Regards,

Anjali Gupta

Re: Match the values from a list in different table.

Anonymous — Wed, 23 Nov 2016 11:45:04 GMT

Can the records in table 2 change, or will they always be?

ABC

PQR - JKL

DEF

Re: Match the values from a list in different table.

Anonymous — Wed, 23 Nov 2016 11:57:20 GMT

If the records in Table 2 do not change or are few, you can do so:

[table1]:

LOAD * Inline [

coluna_1, coluna_2, coluna_3, coluna_4

1, 4, 7, ABC - A

2, 3, 4, ABC - B

6, 6, 2, ABC - C

3, 6, 7, PQR - JKL - Um

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]:

LOAD * Inline [

ColumnA

ABC

PQR - JKL

DEF

];

[teste]:

NoConcatenate

LOAD *,

if(Index(coluna_4,'ABC') > 0 or Index(coluna_4,'PQR - JKL') > 0 or Index(coluna_4,'DEF') > 0, 1, 0) as test

Resident table1;

DROP Table table1;

Re: Match the values from a list in different table.

trdandamudi — Wed, 23 Nov 2016 12:22:11 GMT

One of the way you can do this is as below: (see attached for the code)

Re: Match the values from a list in different table.

Wed, 23 Nov 2016 12:27:08 GMT

Hi Marcio,

The values provided by me is just a sample. In my data, I have 88 different values and which can change later.

So, I need to fetch the data from the excel/table provided.

Thanks and Regards,

Anjali Gupta

Re: Match the values from a list in different table.

Wed, 23 Nov 2016 12:41:00 GMT

Hi Thirumala,

Thanks for the response. I checked the code, but it works for the sample provided. In real, in place of ABC, PQR, JKL, DEF and XYZ we have proper words like Windows, Adobe, Oracle, Power point , Microsoft and so on.

Hence, the Left function (<Column Name>,3) doesn't work.

I have values like 'Windows', 'Windows - Test', 'Microsoft - Power point' and 'Microsoft - Power point - Test' in Column4 and 'Windows' and 'Microsoft - Power point' in ColumnA which needs to be matched accordingly and assigned value 1 in result column.

Thanks and Regards,

Anjali Gupta

Re: Match the values from a list in different table.

maxgro — Wed, 23 Nov 2016 13:04:48 GMT

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;

Re: Match the values from a list in different table.

trdandamudi — Wed, 23 Nov 2016 13:14:40 GMT

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;

Re: Match the values from a list in different table.

MarcoWedel — Wed, 23 Nov 2016 23:16:51 GMT

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

Re: Match the values from a list in different table.

tamilarasu — Thu, 24 Nov 2016 04:59:30 GMT

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:

Re: Match the values from a list in different table.

Thu, 24 Nov 2016 06:12:54 GMT

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

Re: Match the values from a list in different table.

Thu, 24 Nov 2016 09:24:07 GMT

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

Re: Match the values from a list in different table.

MarcoWedel — Fri, 25 Nov 2016 00:10:51 GMT

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

Re: Match the values from a list in different table.

Mon, 28 Nov 2016 06:50:44 GMT

Thanks Marco.

Regards,

Anjali Gupta