Left Join where field is like "x"

[chriys1337]

Hi

I am trying to replace a field from Table A, JUST in case it is the value "UNKNOWN":

The field in Table A is named "Agreement Description"

and I have a Table B with Matchcode (also existing in Table A) and ofcourse the "Agreement Description" (which should be the one which is replacing UNKNOWN values from A).

Does anybody have an Idea how to do it,

I tried two things:

1.

Left Join (A) Load MRP_Matchcode as Matchcode,
AgreementDesc2
Resident Replace_Empty_Agreement_Desc where match(AgreementDesc,'UNKNOWN');

2.Left Join (A) Load MRP_Matchcode as Matchcode,
if(AgreementDesc='UNKNOWN',AgreementDesc2,AgreementDesc) as AgreementDesc
Resident Replace_Empty_Agreement_Desc;

Both does not work .

Please help!

Nachricht geändert durch Christian Schmitz

[sunny_talwar]

Would you be able to show some raw data to see what you have and may be explain what you are trying to get?

[chriys1337]

Hi Sunny,

thanks for your reply, I have added some sample data which describes my problem.

Hope you can help me.

Thanks a lot!

Chris

[sunny_talwar · Accepted solution]

May be this

A:

LOAD * INLINE [

Id, Field, Name, Language

1, UNKNOWN, Christian, ENG

2, BMW, Marius, DE

3, Mercedes, Alex, FR

4, UNKNOWN, Tobias, ESP

5, UNKNOWN, Dominic, DE

6, Porsche, Andreas, DE];

Left Join (A)

LOAD Id,

Field as Field2;

LOAD * INLINE [

Id, Field

1, Audi

4, Mercedes];

FinalTable:

LOAD Id,

If(Field = 'UNKNOWN' and Len(Trim(Field2)) > 0, Field2, Field) as Field,

Name,

Language

Resident A;

DROP Table A;

[chriys1337]

Thánk you!