[SOLVED]: SubStringCount with OR during load ?

[RomanVanK]

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?

[Gysbert_Wassenaar · Accepted solution]

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

[RomanVanK]

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

[rwunderlich]

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]

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.

[rwunderlich]

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