KQL - Remove Duplicates From A List And Sum Values

user_19747366 0
Viewed 73

I have a simple string in KQL that I want to re-format as follows:

Given something in this format like this for example: "ABC-123 (8), ABC-123 (12), ABC-123 (5), DEF (3), DEF (1), GHI (3)", I want to transform it to: "ABC-123 (25), DEF (4), GHI (3)"

Inside the parentheses will always be an integer, and the values preceding the enclosed numbers are just any string.

Basically summing up the numbers in the parentheses for each unique comma separated string that precedes it.

I have tried looking into things like split(), and leveraging array_indexof() to find out the positions of the unique values, but I cannot get it to work exactly.

Could anyone point me in the right direction here?

kql azure-data-explorer
1 Answers

While possible,

datatable(id:int, col:string)
[
    1,  "ABC-123 (8), ABC-123 (12), ABC-123 (5), DEF (3), DEF (1), GHI (3)"
   ,2,  "DEF (3), DEF (4), DEF (5), GHI (4), GHI (5), JKL (7)"
]
| mv-apply kv = extract_all(@"(\S+)\s*\((\d+)\)", col) on 
  (
    summarize v = tostring(sum(toint(kv[1]))) by k = tostring(kv[0])
    | summarize col = array_strcat(make_list(strcat(k, " (", v, ")")), ", ")
  )
id col
1 ABC-123 (25), DEF (4), GHI (3)
2 DEF (12), GHI (9), JKL (7)

Fiddle

storing the result as dictionary seems to be more useful than concatenated it all back to a string.

datatable(id:int, col:string)
[
    1,  "ABC-123 (8), ABC-123 (12), ABC-123 (5), DEF (3), DEF (1), GHI (3)"
   ,2,  "DEF (3), DEF (4), DEF (5), GHI (4), GHI (5), JKL (7)"
]
| mv-apply kv = extract_all(@"(\S+)\s*\((\d+)\)", col) on 
  (
    summarize v = sum(toint(kv[1])) by k = tostring(kv[0])
    | summarize col = make_bag(pack_dictionary(k, v))
  )
id col
1 {"ABC-123":25,"DEF":4,"GHI":3}
2 {"DEF":12,"GHI":9,"JKL":7}

Fiddle

and we can also take an additional step and transform the dictionaries to columns

datatable(id:int, col:string)
[
    1,  "ABC-123 (8), ABC-123 (12), ABC-123 (5), DEF (3), DEF (1), GHI (3)"
   ,2,  "DEF (3), DEF (4), DEF (5), GHI (4), GHI (5), JKL (7)"
]
| mv-apply kv = extract_all(@"(\S+)\s*\((\d+)\)", col) on 
  (
    summarize v = sum(toint(kv[1])) by k = tostring(kv[0])
    | summarize col = make_bag(pack_dictionary(k, v))
  )
| evaluate bag_unpack(col)
id ABC-123 DEF GHI JKL
1 25 4 3
2 12 9 7

Fiddle

user_63364790
Related