Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Subfield with consecutive delimiters - a bug?

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)

Re: Subfield with consecutive delimiters - a bug?

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

Re: Subfield with consecutive delimiters - a bug?

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

Re: Subfield with consecutive delimiters - a bug?

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

Re: Subfield with consecutive delimiters - a bug?

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

Re: Subfield with consecutive delimiters - a bug?

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.

Re: Subfield with consecutive delimiters - a bug?

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

Re: Subfield with consecutive delimiters - a bug?

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.

Re: Subfield with consecutive delimiters - a bug?

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..

Community Browser