Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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
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

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.

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 

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