Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
jduluc12
Creator
Creator

substringcount

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.

9 Replies
its_anandrjs

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'"

];

vvira1316
Specialist II
Specialist II

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)

antoniotiman
Master III
Master III

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

MarcoWedel

please post some more examples and your expected result.

thanks

regards

Marco

shawn-qv
Creator
Creator

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

S.

manoj217
Creator III
Creator III

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

SubField(text, delimiter[, field_no ])

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

MarcoWedel

Hi,

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

QlikCommunity_Thread_277281_Pic1.JPG

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

vvira1316
Specialist II
Specialist II

Hi Jean,

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

Qlik Community Tip: Marking Replies as Correct or Helpful

jduluc12
Creator
Creator
Author

I have to do it in the expression.