Extracting a value from all string records in a column Kusto?

user_17429072 0
Viewed 285

I have the following sample:

let data = datatable(ProducutName:string)
            [
                "Amigo Patchi 50 KG",
                "Amigo Patchi 50 KG",
                "Amigo Patchi 50 KG",
                "Chocolate Bar 25Kg",
                "Chocolate Bar 25Kg",  
                "Chocolate Bar 25Kg",  
                "Brownies CupCakes 100Kg",
                "Brownies CupCakes 100Kg",
                "Brownies CupCakes 100Kg"
            ];

In this sample, there are 3 different product lines:

  • Amigo Patchi
  • Chocolate Bar
  • Brownies Cupcakes

Each of those product lines has in it its name and value. But as you can see, each product has the value and its unit written in a different way. What I am trying to achieve here is to extract this value from each of those records and display it in a new column. So the expected result would be two columns, first column: ProductName; second Column: Value

Here is what I am trying to achieve:

enter image description here

Is there a way to achieve this in Kusto ?

Please take into consideration that the value for each line could change. It is not always the same. So the code that will be written should simply search for this value(searching for an integer) in the string and display it in a new column as shown in the screen shot.

kusto-explorer kql azure-data-explorer
1 Answers
Option 1

parse operator

let data = datatable(ProductName:string)
            [
                "Amigo Patchi 50 KG",
                "Amigo Patchi 50 KG",
                "Amigo Patchi 50 KG",
                "Chocolate Bar 25Kg",
                "Chocolate Bar 25Kg",  
                "Chocolate Bar 25Kg",  
                "Brownies CupCakes 100Kg",
                "Brownies CupCakes 100Kg",
                "Brownies CupCakes 100Kg"
            ];
data
| parse kind=regex ProductName with ProductName @"\s+" Value:int @"\s*" Units
ProductName Value Units
Amigo Patchi 50 KG
Amigo Patchi 50 KG
Amigo Patchi 50 KG
Chocolate Bar 25 Kg
Chocolate Bar 25 Kg
Chocolate Bar 25 Kg
Brownies CupCakes 100 Kg
Brownies CupCakes 100 Kg
Brownies CupCakes 100 Kg

Fiddle

Option 2

extract_all()

let data = datatable(ProductName:string)
            [
                "Amigo Patchi 50 KG",
                "Amigo Patchi 50 KG",
                "Amigo Patchi 50 KG",
                "Chocolate Bar 25Kg",
                "Chocolate Bar 25Kg",  
                "Chocolate Bar 25Kg",  
                "Brownies CupCakes 100Kg",
                "Brownies CupCakes 100Kg",
                "Brownies CupCakes 100Kg"
            ];
data
| project info = extract_all(@"(.*)\s+(\d+)\s*(.*)", ProductName)[0]
| project ProductName = info[0], Value = toint(info[1]), Units = toupper(info[2])
ProductName Value Units
Amigo Patchi 50 KG
Amigo Patchi 50 KG
Amigo Patchi 50 KG
Chocolate Bar 25 KG
Chocolate Bar 25 KG
Chocolate Bar 25 KG
Brownies CupCakes 100 KG
Brownies CupCakes 100 KG
Brownies CupCakes 100 KG

Fiddle

user_63364790
Related