topic Re: SubStringCount with OR during load ? in App Development

[SOLVED]: SubStringCount with OR during load ?

RomanVanK — Sat, 16 Nov 2024 06:28:09 GMT

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?

Re: SubStringCount with OR during load ?

Gysbert_Wassenaar — Wed, 27 Feb 2019 18:22:43 GMT

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

Re: SubStringCount with OR during load ?

RomanVanK — Wed, 27 Feb 2019 18:27:41 GMT

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

Re: SubStringCount with OR during load ?

rwunderlich — Thu, 28 Feb 2019 03:33:17 GMT

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

Re: SubStringCount with OR during load ?

RomanVanK — Thu, 28 Feb 2019 17:26:49 GMT

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.

Re: SubStringCount with OR during load ?

rwunderlich — Thu, 28 Feb 2019 21:07:26 GMT

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