Qlik Community

Qlik Sense App Development

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

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Highlighted
Contributor II
Contributor II

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

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 

Highlighted
Contributor II
Contributor II

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.
Highlighted
MVP & Luminary
MVP & Luminary

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