topic Re: Subfield with consecutive delimiters - a bug? in QlikView

Subfield with consecutive delimiters - a bug?

Tue, 27 Jan 2015 19:50:06 GMT

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

Re: Subfield with consecutive delimiters - a bug?

Colin-Albert — Tue, 27 Jan 2015 20:36:13 GMT

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?

Colin-Albert — Tue, 27 Jan 2015 20:41:00 GMT

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)

Re: Subfield with consecutive delimiters - a bug?

Tue, 27 Jan 2015 20:48:19 GMT

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.

Re: Subfield with consecutive delimiters - a bug?

Tue, 27 Jan 2015 20:50:25 GMT

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?

rwunderlich — Tue, 27 Jan 2015 21:08:26 GMT

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?

Colin-Albert — Tue, 27 Jan 2015 21:10:30 GMT

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?

Peter_Cammaert — Tue, 27 Jan 2015 22:59:13 GMT

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.

Re: Subfield with consecutive delimiters - a bug?

robert_mika — Wed, 28 Jan 2015 05:12:34 GMT

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?

Colin-Albert — Wed, 28 Jan 2015 07:14:38 GMT

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

Re: Subfield with consecutive delimiters - a bug?

engishfaque — Wed, 28 Jan 2015 07:24:20 GMT

Dear John,

Neither it's bug nor it's expression issue.

It's actually happening due to, you are using double space in expression.

Correct:

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

Your script with spaces:

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

Kind regards,

Ishfaque Ahmed

Re: Subfield with consecutive delimiters - a bug?

robert_mika — Wed, 28 Jan 2015 07:31:12 GMT

You are right Colin.

Thanks for the explanation

Re: Subfield with consecutive delimiters - a bug?

MarcoWedel — Wed, 28 Jan 2015 21:26:21 GMT

Hi,

as John stated it's human entered data, I tried to extend your solution to work with any number of spaces (for John's users with itchy fingers 😉

tabStrings:
LOAD *,
    RecNo() as ID
Inline [
string
abcdef                                                    MyData          zzzz
abcdef    MyData zzzz
abcdef    MyData zzzz
abcdef  MyData zzzz
abcdef  MyData zzzz
abcdef MyData zzzz
abcdef    MyData  zzzz
abcdef    MyData  zzzz
abcdef  MyData  zzzz
abcdef  MyData  zzzz
abcdef MyData  zzzz
abcdef    MyData  zzzz
abcdef    MyData  zzzz
abcdef  MyData  zzzz
abcdef  MyData  zzzz
abcdef MyData  zzzz
abcdef    MyData    zzzz
abcdef    MyData    zzzz
abcdef  MyData    zzzz
abcdef  MyData    zzzz
abcdef MyData    zzzz
abcdef    MyData    zzzz
abcdef    MyData    zzzz
abcdef  MyData    zzzz
abcdef  MyData    zzzz
abcdef MyData    zzzz
aa bb cc
aa  bb cc
aa bb  cc
aaa bb  cc
aaa  bb cc
];

SpaceMap:
MAPPING LOAD Repeat(' ',IterNo()+1) , ' '
While IterNo()<MaxSpaces;
LOAD Max(IterNo) as MaxSpaces;
LOAD IterNo() as IterNo
Resident tabStrings
While Index(string, Repeat(' ',IterNo())) or IterNo()=1;

Left Join (tabStrings)
LOAD Distinct
  string,
  SubField(MapSubString('SpaceMap', string),' ',2) as SubString2
Resident tabStrings;

hope this helps

regards

Marco

Re: Subfield with consecutive delimiters - a bug?

rwunderlich — Wed, 28 Jan 2015 22:08:19 GMT

Clever Marco. But wouldn't you agree that

AutoGenerate 9999

is easier to read and faster?

-Rob

Re: Subfield with consecutive delimiters - a bug?

MarcoWedel — Wed, 28 Jan 2015 22:16:45 GMT

Yes, indeed. Good point

Maybe it just proves that one could create a generic solution without assumptions,

even if it's not very practical for a productive solution ...

regards

Marco

Re: Subfield with consecutive delimiters - a bug?

rwunderlich — Wed, 28 Jan 2015 22:18:22 GMT

I like your thinking.

-Rob

Re: Subfield with consecutive delimiters - a bug?

Thu, 29 Jan 2015 01:06:19 GMT

Colin,

I do have to back of somewhat on my sweeping statement about eating delimiters.

I would like and expect a split / subfield command to treat consecutive 'space' delimiters as one delimiter, but I do not want the command to treat consecutive non-space delimiters (such as commas) as one delimiter.

JLR

Re: Subfield with consecutive delimiters - a bug?

Thu, 29 Jan 2015 01:18:56 GMT

Peter,

Java, PHP, C, Perl, Python, etc ... all have a variation on the following:

s.split(" +");

But they use a regular expression to split the string, which is not available to us in QV.

Re: Subfield with consecutive delimiters - a bug?

Thu, 29 Jan 2015 01:20:48 GMT

re: Excel's trim - I did not know that. strange. Not sure I like that.
QV's trim functions as I expect - trim leading / trailing spaces and does nothing to the body of the string.

Re: Subfield with consecutive delimiters - a bug?

Thu, 29 Jan 2015 01:23:35 GMT

Interesting - yet another way to fix the issue. Thanks.

(And Rob, thanks for your "Cookbook". It has been helpful).