topic Re: create one field based on two or more fields in QlikView

create one field based on two or more fields

zwyne — Wed, 25 Nov 2020 16:16:04 GMT

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"

Re: create one field based on two or more fields

sunny_talwar — Tue, 13 Feb 2018 12:18:47 GMT

What is the issue that you are running into?

Re: create one field based on two or more fields

zwyne — Tue, 13 Feb 2018 12:25:43 GMT

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

Re: create one field based on two or more fields

zwyne — Tue, 13 Feb 2018 12:28:27 GMT

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

Re: create one field based on two or more fields

sunny_talwar — Tue, 13 Feb 2018 12:42:14 GMT

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);

Re: create one field based on two or more fields

zwyne — Tue, 13 Feb 2018 12:48:03 GMT

Thanks Sunny, this is what I was looking for.