Table1.FieldA=Table2.FieldB condition in Load Script

[shyamcharan]

Hi Experts,

I have a requirement to create a field values such as 'Within Area' and 'Outside Area'.

The definitions are as below:

IF(Table1.FieldA=Table2.FieldB,'Within Area','Outside Area')

However, I am unable to figure out how to do it.

Any help here is highly appreciated.

Regards,

Shyam.

[sunny_talwar]

May be by doing a join?

Table1:

LOAD * INLINE [

  ID, C1, C2

  1, ABC, 123

    2, DEF, 456

    3, GHI, 789

];

Join(Table1)

LOAD * INLINE [

  ID, C21, C42

  1, ABC, 123

    4, ABC, 456

    5, GRHI, 789

    6, DEF, 456

    7, GHI, 789

];

T1:

LOAD *,

  IF(C21=C1,'Within','NotWithin') AS CA

Resident Table1;

DROP Table Table1;

[shyamcharan]

Thanks for the quick response Sunny.

It is my bad. In my scenario there will not be a direct join between the two tables. They are quite different in the Data Model. There will not be a joining column.

Sorry for the bad example I gave.

I appreciate your further help on this.

Thanks in advance.

Regards,

Shyam.

[sunny_talwar]

Alternatively you can try with Mapping Load to only check IDs in just Table1:

Table1:

LOAD * INLINE [

  ID, C1, C2

  1, ABC, 123

    2, DEF, 456

    3, GHI, 789

];

Table2:

LOAD * INLINE [

  ID, C21, C42

  1, ABC, 123

    4, ABC, 456

    5, GRHI, 789

    6, DEF, 456

    7, GHI, 789

];

MappingTable:

Mapping

LOAD ID,

  C21

Resident Table2;

T1:

LOAD *,

  IF(ApplyMap('MappingTable', ID) = C1,'Within','NotWithin') AS CA

Resident Table1;

DROP Table Table1;

[shyamcharan]

Hi Sunny,

Just got confused. Is the mapping load working on the common field ID in both the tables ?

I will not have a common join field or ID field in both the tables.

The tables would be joined to quite other tables in the data model.

Regards,

Shyam.

[sunny_talwar]

I guess can you share a sample which is more representative of your actual data?

[shyamcharan]

Hi Sunny,

Please accept my appologies for the confusion caused.

Please find the attached new sample.

However, in my actual data model the Table1 and Table 2 are quite apart with more tables in between somthing like the attached example.

Thanks in advance.

Regards,

Shyam.

[sunny_talwar]

What's the output that you are expecting?

[shyamcharan]

Hi Sunny,

I want to have a separate field in Table 1 that has just the values 'Within Area' and 'Outside Area' and that meets the below criteria.

IF(Table1.FieldA=Table2.FieldB,'Within Area','Outside Area')

-Shyam

[sunny_talwar]

Can you check the attached