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: 
Not applicable

Subfield with consecutive delimiters - a bug?

If I have a string with the occasional multiple delimiters between data, Subfield extracts the delimiter and not the expected data.

Example:

=subfield('abcdef    MyData zzzz', ' ' , 2)

I expect the above to return 'MyData', but it returns ' ' (a space)

I could use the following very ugly statement, but I'm not thrilled about doing so, especially since this needs to be done during a load.

=Subfield(TRIM( MID('abcdef    MyData zzzz',INDEX('abcdef    MyData zzzz',' ',1)) ),' ',1)

Based on my experience with many other languages, I expected the subfield to eat the extra consecutive  delimiters, but that is not happening.

Q1) is this the expected Subfield behavior or a bug?

Q2) Does anyone have a better method than my above expression?

TIA,

John

QV 11.20, SR4

21 Replies
Colin-Albert

Your expression ends with ",2" so is asking to extract the data before the second delimiter, hence blank is returned


To get the first value use.   =subfield('abcdef    MyData zzzz', ' ' , 1)

Colin-Albert

You can use replace to remove the double spaces.

You may need to use replace more than once if three or more spaces have been entered

=subfield( replace(replace(replace('abcdef    MyData zzzz'), '  ',' '), '  ',' '), '  ', ' '), ' ' , 2)

Not applicable
Author

In other programming languages I've used, similar functions eat the consecutive delimiters and that was my expectation with this language.

I agree that the next character after the first delimiter is a space and that is what it returns. 

I disagree with how QV has implemented the SubField function.  I believe it should return the first non-delimiter character.

Not applicable
Author

So far, I've only seen two spaces between the substrings, but this is human entered data, so I do not dare make assumptions.

Any idea of of the efficiency of replace vs trim / mid / index?

John

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Are you sure it returns an actual space and not an empty string? My tests show that it returns an empty string, which is what I would expect. I use a mapping table to squeeze out extra blanks when I need to.

SpaceMap:

MAPPING LOAD repeat(' ',recno()+1) , ' '

AutoGenerate 10   // Squeeze up to 11 blanks

;

LOAD subfield( MapSubstring('SpaceMap', myfield), ' ',2) as ....

-Rob

http://masterssummit.com

http://robwunderlich.com

Colin-Albert

You may disagree with how QlikView has implemented subfield, but it works according to the documentation and is unlikely to change.

You can have mulltiple characters as the delimiter and subfield works correctly, but subfield does not support variable length delimiters. If the delimiter length varies, then you will need to correct this before passing the data to the subfield command.

I cannot comment on whether using replace is more efficient than trim/mid/index, but replace is simpler to implement, especially where numerous delimiters may exist.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Can you give me an example of a data processing language that eats consecutive delimiters? Let me translate your example into something that for Subfield() is 100% identical:

abcdef,,,,MyData,zzzz


This tells me that there are 6 fields in your string and that three of them are blank. It would be awfully difficult to split a line like this if SubField would exhibit the behavior you prefer.

BTW I cannot reproduce your results. Whatever I do, SubField() never returns spaces if a space itself is the delimiter. See also Rob's comment below.

SubFieldSplitsOnSpaces thread149791.jpg

robert_mika
Master III
Master III

Subfiled is doing its job as it should.

What surprised me that  TRIM does not do the job.

It "should" (I expected that as Excel do it this way) cut all additional spaces after each word but is only removing empties for whole sentence.

If TRIM was working as in Excel there was no need to use additional functions.

Colin-Albert

In QlikView TRIM removes leading and trailing spaces from a string. It does not remove spaces within a string.

In Excel, trim removes all spaces from a string except for a single space between words.

QlikView is not excel, both have a trim function, but they produce different results.

The F1 help defines the syntax and function of an expression, different languages have subtle differences between similar commands, you cannot assume that functionality will be identical in different programming languages.

QlikView Trim is doing its job as it should, but this is not the same as trim in Excel..