create one field based on two or more fields

[zwyne]

Hello all,

PFA

could you please assist me with this scripting logic below. I want to create a field called Key based on multiple fields within the same table as below:

LOAD [Introducer ID],
if(not IsNull(Upper(Trim(Replace(([Introducer First Name]&[Introducer Middle Name]&[Introducer Last Name]),' ',''))))
or IsNull(Upper(Trim(Replace(([Introducer Legal Name]),' ','')))),
Upper(Trim(Replace(([Introducer First Name]&[Introducer Middle Name]&[Introducer Last Name]),' ','')))),
if(not isNull(Upper(Trim(Replace(([Introducer Legal Name]),' ',''))))
or IsNull(Upper(Trim(Replace(([Introducer First Name]&[Introducer Middle Name]&[Introducer Last Name]),' ',''))),
Upper(Trim(Replace(([Introducer Legal Name]),' ',''))),Key)) as Key 

FROM Sample.xls
(biff, embedded labels, table is Sheet1 $);

Then I want to use this field to match on another DB table like :

LOAD *,
If(Exists(Key), 'Yes', 'No') AS DataCheck;

resident "some table"

[sunny_talwar]

What is the issue that you are running into?

[zwyne]

Hi Sunny, I am unable to populate the field "Key"  by using script that I provided.

[zwyne]

So I want an output like the one below

Key

Neema Augustine Chambo

ANTHONY PAUL JEMBE MKUDE

Jesca Pancras Kahinga

AmaniAmran Mnjagila

KAPWILE IDRIS MSHANGA

LeonardLuwoneko Msigwa

Hamisi Joseph Ngowi

Bammangwato Motors

Bardo

BH Botswana

Broadway Motors

Capital BMW

CCI SA (Holdings) Pty Ltd

Commercial Motors

Continental Iveco

Dennis Toyota

[sunny_talwar · Accepted solution]

Is this what you are looking to get?

Table:

LOAD [Introducer ID],

    [Introducer First Name],

    [Introducer Middle Name],

    [Introducer Last Name],

    [Introducer Legal Name],

    If(not IsNull(Upper(Trim(Replace(([Introducer First Name]&[Introducer Middle Name]&[Introducer Last Name]),' ','')))) or IsNull(Upper(Trim(Replace(([Introducer Legal Name]),' ','')))),

Upper(Trim(Replace(([Introducer First Name]&[Introducer Middle Name]&[Introducer Last Name]),' ',''))),

If(not IsNull(Upper(Trim(Replace(([Introducer Legal Name]),' ','')))) or IsNull(Upper(Trim(Replace(([Introducer First Name]&[Introducer Middle Name]&[Introducer Last Name]),' ','')))),

Upper(Trim(Replace(([Introducer Legal Name]),' ',''))))) as Key 

FROM [..\..\Downloads\Sample (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

[zwyne]

Thanks Sunny, this is what I was looking for.