update result only at inner join condition?

[#1]

i have two table : Table A and Table B

Table A structure

ID   Class  Dept  Indicator

A    C1               Good

B    C2               Bad

C    C1a             Good

D    C2a             Good

Table B structure

ID   Class

A    C1

B    C2

C    C3

D    C4

According to my condition, (department value is update only when Table B.ID= Table A.ID and Table B.Class= Table A.Class and Indicator=Good)

Expect result

Finally table

ID   Class  Dept  Indicator

A    C1       MKT    Good

B    C2       HR       Bad

C    C1a                Good

D    C2a                Good

So i use left join to join two tables first and using if statement to update value, however, i got the result like as below

actutal result

Finally table

ID   Class  Dept  Indicator

A    C1       MKT    Good

B    C2       HR       Bad

C    C1a      MKT     Good

D    C2a      MKT     Good

Should i using inner join first to do transformation first and then concate it back to orginially table? Pls advise.

[#2]

Hi Michael,

I couldn't understand..

From where you are getting values for department? MKT & HR..

Karthik

[#3]

try this

in script write this

TableA:

LOAD * INLINE [

    ID, Class, Dept, Indicator

    A, C1, MKT, Good

    B, C2, HR, Bad

    C, C1a, MKT, Good

    D, C2a, MKT, Good

];

join

TableB:

LOAD *,Class as Class_new INLINE [

    ID, Class

    A, C1

    B, C2

    C, C3

    D, C4

];

then in straight table--

Dimension1=ID

dimension2=Class

expression1=if(Class=Class_new, Dept,' ')

expression2=Indicator

THEN OUTPUT LIKE THIS

ID	Class	Dept	Indicator
			-
A	C1	MKT	Good
B	C2	HR	Bad
C	C1a		Good
D	C2a		Good

SEE ATTACHEMENT

[#4]

using if statement and assign value MKT and HR, such as:

if(indicator='Good', 'MKT', '',

if((indicator='Bad, 'HR', '')) as Dept

[#5]

Thx. As the table is not a  finalized table to use, i would like the result is handled by scripts instead of expression at this stage. 

[#6]

TableA:

LOAD * INLINE [

    ID, Class, Dept, Indicator

    A, C1, MKT, Good

    B, C2, HR, Bad

    C, C1a, MKT, Good

    D, C2a, MKT, Good

];

join

TableB:

LOAD *,Class as Class_new INLINE [

    ID, Class

    A, C1

    B, C2

    C, C3

    D, C4

];

LOAD ID, Class, Dept, Indicator,

if(Class=Class_new, Dept,' ') AS dEPT_NEW

Resident TableA;

[#7]

The dept value is dynamic update to finalized table instead of  input at inline table

[#8]

The dept value is dynamic update to finalized table instead of  input at inline table

[#9 · Accepted solution]

i found the ansewers for myself and i know how to work.

First, creaet a outer join table

Second, create a temp table to store the Primary key.

Third, resident the outer join table, update column value by using if statment and exists function together.

Then, we can drop the table.

Many thanks all of you to give me advice.

Thank you very much