topic Re: Subfield Issue in QlikView

Re: Subfield Issue

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

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,

Re: Subfield Issue

swuehl — Thu, 26 Oct 2017 15:45:45 GMT

Maybe like

LOAD

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

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

Subfield(NAME, ' ',3) as FIELD3

FROM ....;

Re: Subfield Issue

Chanty4u — Fri, 27 Oct 2017 05:56:44 GMT

Here with one more example

Subfield

Re: Subfield Issue

kicchu465 — Fri, 27 Oct 2017 08:27:43 GMT

Hi Stefan,

Thanks for your reply.

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

Old_name	FIELD1	FIELD2	FIELD3
123 AB QWE	123	AB	QWE
ABC D EFG	ABC	D	EFG
ABC XYZ	ABC	XYZ

My expected output should be like

Old_name	FIELD1	FIELD2	FIELD3
123 AB QWE	123	AB	QWE
ABC D EFG	ABC	D	EFG
ABC XYZ	ABC		XYZ

Thanks,

Re: Subfield Issue

swuehl — Fri, 27 Oct 2017 08:51:08 GMT

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?

Re: Subfield Issue

sunny_talwar — Fri, 27 Oct 2017 08:52:35 GMT

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

];

Re: Subfield Issue

kicchu465 — Fri, 27 Oct 2017 09:26:18 GMT

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,

Re: Subfield Issue

Miguel_Angel_Baeyens — Fri, 27 Oct 2017 09:35:07 GMT

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.

Re: Subfield Issue

kicchu465 — Fri, 27 Oct 2017 09:35:53 GMT

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;