Use a field of one table in another table while LOAD

[#1]

I load a script as defined below. I want to add a condition in WHERE clause of Table2 which checks that REFERENCED_NAME of Table0 should not be equal to NAME of Table2 itself. How can I add this condition to where clause statement?

Table0:

LOAD REF_TIME AS REF_TIME,

    NAME AS REFERENCED_NAME,

    REFERENCED_NAME AS REFERENCED_NAME1

FROM

[Data_Source\latestData.xlsx]

(ooxml, embedded labels)

WHERE NAME<>REFERENCED_NAME;

Table1:

LOAD REF_TIME AS REF_TIME1,

    NAME AS REFERENCED_NAME1,

    REFERENCED_NAME AS REFERENCED_NAME2

FROM

[Data_Source\latestData.xlsx]

(ooxml, embedded labels)

WHERE NAME<>REFERENCED_NAME;

Table2:

LOAD REF_TIME AS REF_TIME2,

    NAME AS REFERENCED_NAME2,

    REFERENCED_NAME AS REFERENCED_NAME3

FROM

[Data_Source\latestData.xlsx]

(ooxml, embedded labels)

WHERE NAME<>REFERENCED_NAME and Table0.REFERENCED_NAME <> NAME;

[alexandros17]

This is not possible unless you link (join) the two tables (table0 and table2) in order to reference all the fields in the same table

Hope it helps

[Anonymous]

Hi Nishant,

Use EXISTS function:

Table2:

LOAD REF_TIME AS REF_TIME2,

    NAME AS REFERENCED_NAME2,

    REFERENCED_NAME AS REFERENCED_NAME3

FROM

[Data_Source\latestData.xlsx]

(ooxml, embedded labels)

WHERE

     (NAME<>REFERENCED_NAME) and

     (NOT Exists(REFERENCED_NAME1,NAME)

;

That should do the work.

Best regards,

Janusz

[#4]

Hi Alessandro,

Thanks for your reply. Can you give me an example justifying your statement? My aim is not to load those value of 'NAME' in Table2 which have already been loaded as values of 'NAME' in Table0.

Thanks!

[alexandros17]

I think that Janusz Twardziak solution works fine ...