SOLVED
Home

How can I summarize by case insensitive?

%3CLINGO-SUB%20id%3D%22lingo-sub-354155%22%20slang%3D%22en-US%22%3EHow%20can%20I%20summarize%20by%20case%20insensitive%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354155%22%20slang%3D%22en-US%22%3ECurrently%20I'm%20doing%20'summarize%20by%20tolower(name)'%20but%20I%20suspect%20that%20to%20be%20suboptimal%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354155%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Explorer%20(Kusto)%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%20language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354242%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20summarize%20by%20case%20insensitive%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354242%22%20slang%3D%22en-US%22%3E%3CP%3EDepending%20on%20the%20kind%20of%20aggregation%20you're%20doing%2C%20it%20may%20be%20useful%20to%20first%20summarize%20by%20%3CEM%3Ename%3C%2FEM%3E%20and%20then%20summarize%20again%20by%20%3CEM%3Etolower(name)%3C%2FEM%3E%2C%20so%20that%20your%20query%20converts%20significantly%20fewer%20strings%20to%20lowercase.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoing%20so%20is%20possible%2C%20for%20example%2C%20in%20the%20following%20case%2C%20with%20a%20%3CEM%3Ecount()%3C%2FEM%3E%20aggregation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-function%22%3Edatatable%3C%2FSPAN%3E(s%3Astring)%0A%5B%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22abc%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22def%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22ABC%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22AbC%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22def%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22ABc%22%3C%2FSPAN%3E%0A%5D%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Ec%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Ecount%3C%2FSPAN%3E()%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Es%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Ec%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Esum%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-calculated-column%22%3Ec%3C%2FSPAN%3E)%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Es%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Etolower%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-calculated-column%22%3Es%3C%2FSPAN%3E)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831256%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20summarize%20by%20case%20insensitive%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831256%22%20slang%3D%22en-US%22%3E%3CP%3EI%20found%20another%20problem%20with%20case%20insensitive%2C%20it%20doesn't%20seem%20to%20work%20on%20letter%20such%20as%20%22%C3%85%20%C3%84%20%C3%96%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE.g%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Eprint%3C%2FSPAN%3E%3CSPAN%3E%22%C3%96VRIGT%22%3C%2FSPAN%3E%3CSPAN%3E%3D~%20%3C%2FSPAN%3E%3CSPAN%3E%22%C3%B6vrigt%22%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%20false%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3Eprint%3C%2FSPAN%3E%3CSPAN%3E%22VRIGT%22%3C%2FSPAN%3E%3CSPAN%3E%3D~%20%3C%2FSPAN%3E%3CSPAN%3E%22vrigt%22%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%20true%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20instruct%20Data%20Explorer%20to%20use%20Invariantculure%20etc%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
abergs
Occasional Contributor
Currently I'm doing 'summarize by tolower(name)' but I suspect that to be suboptimal
2 Replies
Solution

Depending on the kind of aggregation you're doing, it may be useful to first summarize by name and then summarize again by tolower(name), so that your query converts significantly fewer strings to lowercase.

 

Doing so is possible, for example, in the following case, with a count() aggregation:

 

datatable(s:string)
[
    "abc",
    "def",
    "ABC",
    "AbC",
    "def",
    "ABc"
]
| summarize c = count() by s
| summarize c = sum(c) by s = tolower(s)

 

Highlighted

I found another problem with case insensitive, it doesn't seem to work on letter such as "Å Ä Ö".

 

E.g

 

print "ÖVRIGT" =~ "övrigt" // false

print "VRIGT" =~ "vrigt" // true

 

Is it possible to instruct Data Explorer to use Invariantculure etc?