Update field if value matches/exists in other table

[felipe_oliveira]

Hi masters

I have the following scenario.

Map_Program:

Mapping load * inline [

Program_Code, Program

104, XXXX

111, YYYY

129, ZZZZ

175, WWW

];

Table1:

Load * INLINE [

Location, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

Table2:

Load Account,

         Customer,

         Program_Name,

         ApplyMap('Map_Program',Program_Name,'Other') as Program,

         Period,

         Location,

         Amount

         Resident DWH;

For the remaining "Other" resulting from the ApplyMap above, I would like to update the Program column (='Direct') if location from Table2 = location from Table1.

How can I do in the load statement?

Thanks

[sunny_talwar]

May be this

Map_Program:

Mapping load * inline [

Program_Code, Program

104, XXXX

111, YYYY

129, ZZZZ

175, WWW

];

Table1:

Load * INLINE [

Location, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

Table2:

Load Account,

        Customer,

        Program_Name,

        ApplyMap('Map_Program',Program_Name, If(Exists(Location), 'Direct', 'Other')) as Program,

        Period,

        Location,

        Amount

Resident DWH;

[sunny_talwar · Accepted solution]

Or may be this

Map_Program:

Mapping load * inline [

Program_Code, Program

104, XXXX

111, YYYY

129, ZZZZ

175, WWW

];

Table1:

Load * INLINE [

Location_Name, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

Table2:

Load Account,

        Customer,

        Program_Name,

        ApplyMap('Map_Program',Program_Name, If(Exists(Location_Name, Location), 'Direct', 'Other')) as Program,

        Period,

        Location,

        Amount

Resident DWH;

[its_anandrjs]

Few questions

1. In Program_Name what is the data it is code or Program name

2. After apply mapping Programe name = location if it is then try this

Table1:

Mapping

Load * INLINE [

Location, Segment

AAAA, Direct

BBBB, Direct

CCCC, Direct

DDDD, Direct

EEEE, Direct

FFFF, Direct

];

ApplyMap('Map_Program',Program_Name,ApplyMap('Table1',Location)) as Program,

[felipe_oliveira]

Thanks Sunny! It worked perfectly!