Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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.
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
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
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.
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.
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.
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..