topic Re: substringcount in QlikView

substringcount

jduluc12 — Sun, 08 Oct 2017 18:10:30 GMT

Hi,

I need to find out this substring

'abc'

from a text which is like this

'qwe','wer','abc'.

Is it possible using substringcount?

I am asking becasue the there are single quotes in the main text 'qwe','wer','abc' so substringcount does not recognize it as a single text in its 1st param.

Re: substringcount

its_anandrjs — Sun, 08 Oct 2017 18:32:15 GMT

You can try this way by replace comma with blanks just for finding the "abc" string in column.

LOAD *,If(NewStrData > 0,'Found','NotFound') as abcFoundflag;

LOAD *,FindOneOf(replace(StrData,chr(39),''),'abc') as NewStrData;

load * Inline

[

StrData

"'qwe','wer','abc'"

"'qwe','ret'"

];

Re: substringcount

vvira1316 — Sun, 08 Oct 2017 18:45:46 GMT

Hi,

Have you tried

String functions ‒ QlikView

SubStringCount

SubstringCount() returns the number of occurrences of the specified substring in the input string text. If there is no match, 0 is returned.

SubStringCount( text , substring)

Re: substringcount

antoniotiman — Sun, 08 Oct 2017 20:10:02 GMT

SubStringCount(Field,Chr(39)&'abc'&Chr(39))

Re: substringcount

MarcoWedel — Sun, 08 Oct 2017 20:42:43 GMT

please post some more examples and your expected result.

thanks

regards

Marco

Re: substringcount

shawn-qv — Mon, 09 Oct 2017 00:28:55 GMT

Alternatively, you may decide to remove/replace the single quotation marks from the Source string (but leave the comma).

Re: substringcount

manoj217 — Mon, 09 Oct 2017 02:58:50 GMT

for this try subfield function to get a part of text from a string

SubField(text, delimiter[, field_no ])

subfield('qwe','wer','abc',',',3)

Re: substringcount

MarcoWedel — Mon, 09 Oct 2017 20:19:15 GMT

Hi,

maybe this example helps you to describe what different result you would expect of the SubStringCount() function:

table1:

LOAD *,

SubStringCount(text,substring) as SubstrCnt

Inline [

text, substring

"'qwe','wer','abc'" , "'abc'"

"'qwe','wer','abc'" , abc

"'qwe','wer', abc" , "'abc'"

"'qwe','wer', abc" , abc

"'qwe','abc','wer','abc'" , "'abc'"

"'qwe','abc','wer','abc'" , abc

];

hope this helps

regards

Marco

Re: substringcount

vvira1316 — Tue, 10 Oct 2017 18:37:47 GMT

Hi Jean,

If you have found answer to your question then please close the thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: substringcount

jduluc12 — Thu, 12 Oct 2017 11:32:41 GMT

I have to do it in the expression.