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

[lironbaram]

hi

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 

[#3]

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

[Anonymous]

An idea

[#5]

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

[Anonymous]

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

ABC

PQR - JKL

DEF

[Anonymous]

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;

[trdandamudi]

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

[#9]

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

[#10]

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