topic Splitting a field name in QlikView

Splitting a field name

Anonymous — Fri, 20 Mar 2015 14:05:38 GMT

Hi all,

I am having an issue with loading a field name as two separate fields.

Basically, the field name 'Version' is made up of two strings separated by a space. The length and format of both strings can differ and in terms of length and character composition (mix of numbers and letters).

I have tried to use the Trim and Left/Right functions to split Version into two different fields Version_Type and Version_Number taking the two strings, but because the number of characters differ in each item I can't apply a consistent rule.

The fields appears like this:

Version
DWSZPL X425
LTRB 31JK89
WSRTBBX 90D2Y
JTM PW8

I need to cut them at the space and load them as follows:

Version Type	Version Number
DWSZPL	X425
LTRB	31JK89
WSRTBBX	90D2Y
JTM	PW8

Any suggestions on the best way to do this?

Thanks in advance.

Re: Splitting a field name

Gysbert_Wassenaar — Fri, 20 Mar 2015 14:07:32 GMT

subfield(Version,' ',1) As [Version Type]

subfield(Version,' ',2) As [Version Number]

Re: Splitting a field name

Anonymous — Fri, 20 Mar 2015 14:09:55 GMT

Your solution is the subfield() function:

subfield(Version, ' ', 1) as "Version Type"

subfield(Version, ' ', 2) as "Version Number"

Re: Splitting a field name

Anonymous — Fri, 20 Mar 2015 14:39:42 GMT

Thank you both for the quick replies.

I seem to be getting null values. I have seen that here may be some data items which contain two or three spaces, i.e. the Version Type portion would be made up of two or three strings. E.g:

Version Type	Version Number
CFFR WDS	8UHJ
CQ PFTH ZSD	BNHAS

Would this cause potential issues when using subfield in this manner or should it still display results even if not completely accurate?

The final space (if there are multiple) will always be the separator between Version Type and Version Number.

Re: Splitting a field name

Peter_Cammaert — Fri, 20 Mar 2015 14:53:16 GMT

What about:

mid(Version, 1, index(Version, ' ', -1)-1) AS [Version Type],

mid(Version, index(Version, ' ', -1)+1) AS [Version Number],

[edit] or:

left(Version, index(Version, ' ', -1)-1) AS [Version Type],

mid(Version, index(Version, ' ', -1)+1) AS [Version Number],

[edit2] Indeed, omit the last space...

Re: Splitting a field name

robert_mika — Fri, 20 Mar 2015 15:00:32 GMT

TextBetween(Version,'',' ') As [Version Type]

TextBetween(Version,' ','') As [Version Number]

Examples:

Re: Splitting a field name

giakoum — Fri, 20 Mar 2015 15:05:13 GMT

This is a small addition to make it work for the last space :

=subfield(Version, ' ', SubStringCount(Version, ' ')) as [Version Type]

=subfield(Version, ' ', SubStringCount(Version, ' ')+1) as [Version Number]

Re: Splitting a field name

Anonymous — Fri, 20 Mar 2015 15:30:55 GMT

OK, in this case subfield() is good only for the Version number:
subfield(Version, ' ', -1) as "Version Number"

As for the Version Type, it could be
left(Version, index(Version, subfield(Version', ' ', -1))-1)

Re: Splitting a field name

Anonymous — Fri, 20 Mar 2015 15:41:01 GMT

Thanks Ioannis.

This has sorted it.

Re: Splitting a field name

Fri, 20 Mar 2015 15:43:19 GMT

Better ask the source team to send the file in fixed width or delimited by some symbol.

Re: Splitting a field name

Peter_Cammaert — Fri, 20 Mar 2015 15:50:24 GMT

You don't like the mid() and left() functions? They do work though...

Re: Splitting a field name

Anonymous — Fri, 20 Mar 2015 15:50:32 GMT

Sorry, I don't think it works as requested.

This (for Version Type)

subfield('AAA BBB CCC', ' ', SubStringCount('AAA BBB CCC', ' '))

returns 'BBB' instead of expected 'AAA BBB'

Re: Splitting a field name

Fri, 20 Mar 2015 15:57:25 GMT

subfield(Version, ' ', SubStringCount(Version, ' ')) as [Version Type]

subfield(Version, ' ', SubStringCount(Version, ' ')+1) as [Version Number]