Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kicchu465
Creator
Creator

Re: Subfield Issue

Hi all,

I have a query regarding subfiled.

Please find the below Input data.

Name:

ABC D EFG

ABC XYZ

123 AB QWE

The Query is, I need to split the above data into three different filed using Subfield function or if there any other way to acheive this.

Reagrds,

Kb

8 Replies
swuehl
MVP
MVP

Maybe like

LOAD     

     Subfield(NAME, ' ',1) as FIELD1,

     Subfield(NAME, ' ',2) as FIELD2,

     Subfield(NAME, ' ',3) as FIELD3

FROM ....;

Chanty4u
MVP
MVP

Here with one more example

Subfield

kicchu465
Creator
Creator
Author

Hi Stefan,

Thanks for your reply.

By using the above code the result would like as below.

  

Old_nameFIELD1FIELD2FIELD3
123 AB QWE123ABQWE
ABC D EFGABCDEFG
ABC XYZABCXYZ

My  expected output should be like

  

Old_nameFIELD1FIELD2FIELD3
123 AB QWE123ABQWE
ABC D EFGABCDEFG
ABC XYZABC XYZ

Thanks,

Kb

swuehl
MVP
MVP

What determines that the two part string need to be split and assigned to FIELD1 and FIELD3 instead of FIELD1 and FIELD2? Is this a general rule or based on the string content?

sunny_talwar

May be this

Table:

LOAD Name,

Subfield(Name, ' ', 1) as Field1,

If(SubStringCount(Trim(Name), ' ') = 2, Subfield(Name, ' ', 2)) as Field2,

Subfield(Name, ' ', -1) as Field3;

LOAD * Inline [

Name

ABC D EFG

ABC XYZ

123 AB QWE

];

kicchu465
Creator
Creator
Author

Hi Stefan,

The rule is splitting a Name into forename,Middle Name and Surname.

As we know some people will use their Middle name in their name and few will not.

So I wanted to split the Name Field into  forename,Middle Name and Surname.

Thanks,

Kb

Miguel_Angel_Baeyens

I agree with Stefan, you need to determine which field stores what. My first name (Miguel Angel) is two words separated by a space, not a dash, very common in Spain and other Latin American countries, and I don't have a middle name. My last name (Baeyens de Arce) has 3 words (which again is very common in Spanish speaking countries), separated by space as well, and they all should go in the "LastName" field.

Just using SubField(), it would display wrong and incomplete following that logic: (first name Miguel -wrong-, middle name Angel -wrong-, last name Baeyens -wrong-, second part of last name "de Arce" missing -wrong-).

Even if you had a list of possible names, how can the application "guess" which part belongs to the first (composite or single), middle and last, and how long the last name can be (e.g.: one, two, three or more words)?

If you are splitting the names to have less unique values and optimize performance, that's OK, but on the front end you will have to concatenate them again. If one of those fields is empty, replace by a space, otherwise display the value.

kicchu465
Creator
Creator
Author

Hi Sunny,

I have tried the above code and it has worked.

I have also created one more for resolving this please find below.

FullName:

LOAD * Inline [

Name

ABC D EFG

ABC XYZ

123 AB QWE

];

Result:

Load *,

SubField(Name, ' ',1) as Sub_FirstName,

MID(Name,index(Name,' ',1)+1,index(Name,' ',2)-(index(Name,' ',1)+1)) as Sub_MiddleName,

SubField(Name, ' ',-1) as Sub_Surname

Resident FullName;

Drop Table FullName;