Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

RomanVanK
New Contributor II

[SOLVED]: SubStringCount with OR during load ?

Solution: SubStringCount is case-sensitive.

I am trying to create an extra column in load. The value should be "Yes" if one of the existing columns contains one of two substrings.

LOAD
    Name,
    "Type",
    "Year",
    Hits,
    if((SubStringCount([Name],'community')>0 OR SubStringCount([Name],'operative')>0),'Yes','No') AS [CC],
    Latlong
FROM [lib://AttachedFiles/MyFile.csv]
  • New column gets added. ✔
  • Rows where column Name contains "operative" have value "Yes" in the new column. ✔
  • Rows where column Name contains "community" have value "No" in the new column. ✖

How do I modify my on-the-fly column creation expression in order to get it right?

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: SubStringCount with OR during load ?

Substringcount is case sensitive. Are you sure the records that are missing do have the complete lowercase 'community' value in the field Name?


talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: SubStringCount with OR during load ?

Substringcount is case sensitive. Are you sure the records that are missing do have the complete lowercase 'community' value in the field Name?


talk is cheap, supply exceeds demand
RomanVanK
New Contributor II

Re: SubStringCount with OR during load ?

I have just spotted the case disagreement, fixed it, and came here to share my discovery. Thank you for beating me to it.

Highlighted
MVP & Luminary
MVP & Luminary

Re: SubStringCount with OR during load ?

Here are some case insensitive ways to write the same logic.

if( WIldMatch([Name],'*community*', '*operative*'),'Yes','No') AS [CC]

if( [Name] like *community*' or [Name] like '*operative*', 'Yes', 'No') AS [CC]

-Rob 

RomanVanK
New Contributor II

Re: SubStringCount with OR during load ?

Thank you for the education, Rob.

Speaking of WildMatch(), I was looking for a list of Qlik functions with an easy search (like api.jquery.com), but all I could find was a function directory, where one is expected to be pre-armed with the name or the purpose of a function in order to find it. It will take me some time to learn the vocabulary.
MVP & Luminary
MVP & Luminary

Re: SubStringCount with OR during load ?

I agree that the on-line help layout sometimes just doesn't do the trick.  I find the PDF versions much easier to browse and search.  You can download PDFs from the "Guides" section found at the bottom of the help navigation menu for each product.

-Rob

2019-02-28_13-05-32.png