topic Re: Combining two fields query in QlikView

Combining two fields query

Tue, 25 Aug 2015 06:06:01 GMT

Hi there,

I am combining two fields in my edit script as such:

Load

UserID & ' - ' & ContactPerson as Contact_Person_ID

From.....

However, if the UserID field or the ContactPerson field is empty it brings back a null value.

How would I edit this to make sure that either the UserId or the ContactPerson or both combined together will show up as the field "Contact_Person_ID"?

Thank you.

Re: Combining two fields query

kaushiknsolanki — Tue, 25 Aug 2015 06:08:39 GMT

Hi,

Do you mean that you don't need the key when either UserID or ContactPerson field is null?

If yes then you can use the where clause in your load script to eliminate those records.

Regards,

Kaushik Solanki

Re: Combining two fields query

ecolomer — Tue, 25 Aug 2015 06:10:05 GMT

You can assign 0 (zero) to null value for ID and ' ' (blank value) for Contact_Person

Also you can select only value not null

For that you can ask in the script If(IsNull(ID), 0, ....

Re: Combining two fields query

Tue, 25 Aug 2015 06:24:01 GMT

Hi,

Thank you for your response.

I don't want to eliminate any values.

I want so that the combined "Contact_Person_ID" field brings up either both the Contact Person and ID or the Contact Person or the ID. Because what is happening is that sometimes there might be a UserID but not a Contact Person, but I still want the UserID to show.

Re: Combining two fields query

maleksafa — Tue, 25 Aug 2015 06:27:03 GMT

Load

if(Not isnull(UserID),UserID,'') & ' - ' & if(Not isnull(ContactPerson),ContactPerson,'') as Contact_Person_ID

From.....

so basically i have added a condition to check if the fields are null, if they are not null (using the Not isnull()) then it will use the value of the field else (if null) it will be kept as blank and not null

Re: Combining two fields query

kaushiknsolanki — Tue, 25 Aug 2015 06:28:36 GMT

Hi,

Could you please send us your script and let us know what output you are getting and what is expected.

Regards,

Kaushik Solanki

Re: Combining two fields query

jonathandienst — Tue, 25 Aug 2015 06:30:46 GMT

The expression evaluates to null if any part is null. You can use one of these to handle the null case batter:

If(IsNull(UserID), '', UserID) & ' - ' & If(IsNull(ContactPerson), '', ContactPerson)

If(Len(UserID) > 0, UserID, '') & ' - ' & If(Len(ContactPerson) > 0, ContactPerson, '')

Re: Combining two fields query

Kushal_Chawda — Tue, 25 Aug 2015 06:32:19 GMT

try something like below

test:

LOAD *, if(len(trim(UserID))=0 or len(trim(ContactPerson))=0 ,trim(UserID)&trim(ContactPerson),

if(len(trim(UserID))>0 and len(trim(ContactPerson))>0,UserID&'-'&ContactPerson)) as New Inline [

UserID, ContactPerson

1, A

, B

3,C,

4,D ];

Re: Combining two fields query

sasiparupudi1 — Tue, 25 Aug 2015 06:41:30 GMT

using Kush's Data, please try

LOAD UserID, ContactPerson,Text(replace(trim(UserID &' '& ContactPerson),' ','-')) as Contact_Person_ID

Inline [

UserID, ContactPerson

1, A

, B

3,C,

4,D ];

Re: Combining two fields query

maxgro — Tue, 25 Aug 2015 06:48:35 GMT

t: // test data

load if(UserID='null', null(), UserID) as UserID, if(ContactPerson='null', null(), ContactPerson) as ContactPerson

inline [

UserID , ContactPerson

a, null

null, b

null, null

c,d

];

test:

NoConcatenate load

if(len(trim(UserID))=0 and len(trim(ContactPerson))=0, 'Both NULL',

if(len(trim(UserID))=0 or len(trim(ContactPerson))=0, UserID & ContactPerson,

UserID & ' - ' & ContactPerson

)) as Contact_Person_ID

Resident t;

drop Table t;

Re: Combining two fields query

Wed, 26 Aug 2015 12:02:05 GMT

Thank you all for assistance