Kusto Remove partial duplicate

user_14702608 0
Viewed 368

With the table storedata, I am trying to remove the row "Target TargetCheese 4" The logic here is if there are two or more entries for the same product at a given store it will choose the StoreNumber which best fits that store based on the other rows. If the StoreNumber doesn't match but it is not a duplicate Product then the number will not change; for example SafewayEggs will have StoreNumber equal to 1 even though there are more Safeway entries with the StoreNumber as 6 because there is only one row of SafewayEggs.

let storedata=
datatable (Store:string,    Product:string  ,StoreNumber:string)
["Target",  "TargetCheese", "4",
"Target",   "TargetCheese", "5",
"Target",   "TargetApple",  "5",
"Target",   "TargetCorn",   "5",
"Target",   "TargetEggs",   "5",
"Kroger",   "KrogerApple",  "2",
"Kroger",   "KrogerCorn",   "2",
"Kroger",   "KrogerEggs",   "2",
"Safeway",  "SafewayApple", "6",
"Safeway",  "SafewayCorn",  "6",
"Safeway",   "SafewayEggs", "1"
];

I am hoping to see this result table from the storedata table:

Store   Product StoreNumber
Target  TargetCheese 5
Target  TargetApple 5
Target  TargetCorn  5
Target  TargetEggs  5
Kroger  KrogerApple 2
Kroger  KrogerCorn  2
Kroger  KrogerEggs  2
Safeway SafewayApple 6
Safeway SafewayCorn 6
Safeway SafewayEggs 1
kql azure-data-explorer relational-database
2 Answers

You might need different steps:

  1. find the "best fit" StoreNumber - in my example below, the one with most occurences, use arg_max
  2. dataset that has to be cleaned up with (1), more than 1 occurence per store and product, use count
  3. the dataset that needs no cleanup, only one occurence per store and product
  4. a union of (3) and the corrected dataset
let storedata=
datatable (Store:string,    Product:string  ,StoreNumber:string)
["Target",  "TargetCheese", "5",
"Target",   "TargetCheese", "4",
"Target",   "TargetApple",  "5",
"Target",   "TargetCorn",   "5",
"Target",   "TargetEggs",   "5",
"Kroger",   "KrogerApple",  "2",
"Kroger",   "KrogerCorn",   "2",
"Kroger",   "KrogerEggs",   "2",
"Safeway",  "SafewayApple", "6",
"Safeway",  "SafewayCorn",  "6",
"Safeway",   "SafewayEggs", "1"
];
// (1) evaluate best-fit StoreNumber
let storenumber =
storedata
| order by Store,  StoreNumber
| summarize occ= count () by Store, StoreNumber
| summarize  arg_max(occ, *) by Store;
// (2) dataset to be cleaned = more than one occurence per store and product
let cleanup =
storedata
| summarize occ = count () by Store,  Product
| where occ > 1
| project-away occ;
// (3) dataset with only one occurrence 
let okdata =
storedata
| summarize occ= count () by Store, Product
| where occ==1
| project-away occ;
// (4) final dataset 
let res1 =storenumber
| join cleanup on Store
| project Store, Product, StoreNumber;
let res2 = storedata
| join okdata on Store, Product
| project-away Store1, Product1;
res1
| union res2;
user_96787620

I don't understand the logic you want for removing the following line:

"Target", "TargetCheese", "4"

But if you want to take the highest value for Store and Product, then you can use the following approach:

storedata
| summarize max(StoreNumber) by Store, Product
user_21450970
Related