Compare 2 fields & display both records.

[divya_anand]

Hi all,

I am seeking a solution where I can compare the fields code1 & code 2 which belong to same table and if it matches then the output should be both the keys.

For eg. in the below screenshot, since 111 matches in code1 & code2, It should display "A01" and "B02".

Can someone please help me here.

Thank you very much.

[tamilarasu]

Hi Divya,

Try like this

Data:
LOAD Code1,

     Code2,

     Key1
From Source;

Left Join
Load Code2 as Code1,
Key1 as TempKey
Resident Data;

Result:
Load Code1,
Code2,
Key1 & If(Not isnull(TempKey),',' & TempKey) as Key1
Resident Data;

DROP Table Data;

[divya_anand]

Hi Tamil,

what if there are more fields instead of just one (key1)?

So, if code1 = code2 then both the records with respect to code1 & code2 are to be displayed in table.

Thank you for the response.

[tamilarasu]

Hello Divya,

Maybe this?

Data:

LOAD * INLINE [
Code1, Code2, Key1, Key2, Key3
111, 272, 001, 02, 02
112, 282, A02, 04, 02
113, 293, A03, 05, 03
114, 263, A04, 06, 04
122, 111, B02, 07, 05
];

Left Join
Load Code2 as Code1,
Key1 & ', ' & Key2 & ', ' & Key3  as TempKey
Resident Data;

Result:
Load Code1,
Code2,
Key1 & If(Not IsNull(TempKey),',' & TempKey)  as Key1
Resident Data;

DROP Table Data;

Output:

If this is not your expectation, please post a sample and expected result. Thanks

[divya_anand]

Hi Tamil,

Thank you for the response again.

What I'm looking for is- Say I have data as given below.

So, when I create a straight table and if I search for 111 in code1, I see only that record in the straight table:

I need help in finding a way to see both records where code1=111 and code2=111 like this:

So, the records where there is a match in code1 & code2, should display both the records.

In case you need more information, kindly let me know.

Thank you.

[tamilarasu]

stalwar1

Do you have any idea

[sunny_talwar · Accepted solution]

May be using LinkTable?

Table:

LOAD RowNo() as Key,

  *;

LOAD * INLINE [

    Code1, Code2, Key1, location, description

    111, 272, A01, X, madsmadbh

    112, 282, A02, X, adshagdhsa

    113, 293, A03, X, adkjsadh

    113, 299, A03, X, asdsaf

    114, 263, A04, X, sfdvgszf

    115, 219, A05, X, sfdfgstg

    116, 200, A06, X, sdfgdsrsd

    121, 271, B01, Y, sfdbgfd

    122, 111, B02, Y, sgdgsyhdbrv

    123, 290, B03, Y, sfsghtum

    123, 296, B03, Y, jhjfghdfby

    124, 264, B04, Y, turhd

    125, 113, B05, Y, btjhdg

    126, 204, B06, Y, uyergfd

];

LinkTable:

LOAD DISTINCT Code1 as Code,

  Key

Resident Table;

Concatenate (LinkTable)

LOAD DISTINCT Code2 as Code,

  Key

Resident Table;

[tamilarasu]

Good one. I too had a thought of creating a link lin table. I suppose Divya wants to make a selections in Code1 and Code2 field and vice versa. Let's wait.

[divya_anand]

Hi Sunny,

This is perfect. Thank you very much. helped me a lot.

[divya_anand]

Hi Tamil,

Thank you very much. Really appreciate it.