CODEWITHSUNDEEP
kql
shani klein
shani klein

Reputation: 344

KQL- make list for numeric values only

I'm new at QKL and got stuck doing some complex aggregations. I have a dataset with two columns - one column named key and one value. I want to make list of all the numeric values with the same key , but keep non-numeric value as seperate .

For example if my dataset looks like this

key value
location "CA"
location "LA"
code "2"
code "3"
IP "192.143.204.19"

Meaning I want to get the following table

key value
location "CA"
location "LA"
code ["2","3"]
IP "192.143.204.19"

summarize vals=make_set(val) by key won't work because it will combine "LA" and "CA" and when trying to do make_set_if I got empty lists in the non-numeric values.

Has anyone have an idea how to solve it ? Thank you!

Upvotes: 0

Views: 1188

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

you could try this (handling both cases separately, then unioning the results):

let T = datatable(key:string, value:string)
[
    "location", "CA",
    "location", "LA",
    "code", "2",
    "code", "3",
    "IP",   "192.143.204.19",
]
| extend long_value = tolong(value)
;
T
| where isnull(long_value)
| union (
    T
    | where isnotnull(long_value)
    | summarize value = tostring(make_list(long_value)) by key
)
| project key, value
key value
location CA
location LA
IP 192.143.204.19
code [2,3]

Upvotes: 2

Related Questions