Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

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
Highlighted
MVP
MVP

Re: Subfield Issue

Maybe like

LOAD     

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

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

     Subfield(NAME, ' ',3) as FIELD3

FROM ....;

Highlighted
Esteemed Contributor III

Re: Subfield Issue

Here with one more example

Subfield

Highlighted
Contributor

Re: Subfield Issue

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

Highlighted
MVP
MVP

Re: Subfield Issue

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?

Highlighted

Re: Subfield Issue

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

];

Highlighted
Contributor

Re: Subfield Issue

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

Re: Subfield Issue

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.

Highlighted
Contributor

Re: Subfield Issue

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;