Compare two fields

[cbushey1]

Hi there,

Here is some sample data. What I am trying to do is if the two fields are equal, i.e. field 1 = field 2, I then want to see if any of the values in Compare are also in Field 1. This is id dependant.

Sample:

Id, Field1,

A, 5

A, 10

A, 15

A, 20

B, 2

B, 3

List:

Field2, Compare

20, 5

20, 7

20, 9

20, 11

So in this case I can see 20 in field1 = 20 in field 2, AND 5 in Compare = 5 in Field 1. I would want to return this result and not others. It is important to also identify that 20 was the cause.

Been racking my brain on this all day. Appreciate the help.

[cbushey1]

Hi Pradosh,

Sorry for the delay but I hadn't tried your suggestion as I was working each suggestion in the order they came in. After reviewing your suggestion, it would seem it doesn't quite return the results I was expecting. Using the example code you provided, the results only include Id and field1 or A and 20. The expected results should be ID = A, field 1 = 20, and 'some name' = 5. The point was to see that 5 was also a match in field 1 so that is why it should be returned separately.

Thanks for putting some thought and effort into my problem though, I truly appreciate it.

[pradosh_thakur]

Hi

i thought you only need ID, Field1. I am happy that you found your solution. Still if you just add "compare" in the last table  it will show that too. I have modified that and changes are in red. Please find the attached. Try it out and see if it helps you or optimizes.

INPUT:

LOAD * INLINE [

Id, Field1

A, 5

A, 10

A, 15

A, 20

B, 2

B, 3

];

inner join

List:

LOAD  * INLINE [

Field2, Compare

20, 5

20, 7

20, 9

20, 11

3, 2

];

inner join

load distinct Id ,Field2 as sw1

Resident INPUT

where Match(Field1,Field2)>0 ;

load Id,sw1 as Field1,Compare as "Some field"

Resident INPUT

where Field1=Compare;

drop TABLE INPUT;

regards

Pradosh