Combining two fields query

[#1]

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.

[kaushiknsolanki]

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

[ecolomer]

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, ....

[#4]

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.

[maleksafa]

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

[kaushiknsolanki]

Hi,

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

Regards,

Kaushik Solanki

[jonathandienst · Accepted solution]

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, '')

[Kushal_Chawda]

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

2,

, B

3,C,

4,D ];

[sasiparupudi1]

using Kush's Data, please try

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

Inline [

UserID, ContactPerson

1, A

2,

, B

3,C,

4,D ];

[maxgro]

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;