Compare 2 Text Fields from 2 different tables

[ariel_klien]

Hello all,

I have 3 tables (A,B,C) and i want to compare text field from a table to text field from C table.

for example:

A:

LOAD * INLINE [

    ID, TEXT1

    1, aaaa

    2, yyyy

    3, cccc

    4, dddd

    5, eeee

    6, bbbb

];

B:

LOAD * INLINE [

    ID, ID2

    1, 6

    2, 5

    3, 4

    4, 3

    5, 2

    6, 1

];

C:

LOAD * INLINE [

    ID2, TEXT2

    1, bbbb

    2, aaaa

    3, cccc

    4, rrrr

    5, hhhh

    6, ffff

];

I tried to use this function if(TEXT1=TEXT2,null(),'Different') as expression in my Straight table and it works perfectly with the example tables.

But when I tried to put in in my real tables (every table has 2,000,000 records) it stuck.

Any suggestions?

BR

Ariel

[jagan · Accepted solution]

Hi,

Instead of using this in straight table, do the same in script, so that the performance would be better.

A:

LOAD * INLINE [

    ID, TEXT1

    1, aaaa

    2, yyyy

    3, cccc

    4, dddd

    5, eeee

    6, bbbb

];

LEFT JOIN (A)

LOAD * INLINE [

    ID, ID2

    1, 6

    2, 5

    3, 4

    4, 3

    5, 2

    6, 1

];

LEFT JOIN (A)

LOAD * INLINE [

    ID2, TEXT2

    1, bbbb

    2, aaaa

    3, cccc

    4, rrrr

    5, hhhh

    6, ffff

];

Data:

LOAD

     *,

if(TEXT1=TEXT2,null(),'Different')  AS Status

RESIDENT A;

DROP TABLE A;

Hope this helps you.

Regards,

Jagan.

[ariel_klien]

Working fine

Thanks,

Ariel