Forum Discussion
If/Then/Or Statement Based on Values in Column Ranges?
Hi all!
I need help with the below if/then or statement that seems simple in theory but I am struggling to write it in KQL. I am looking to get the results in the red "Adjusted Amount" column with a statement similar to this:
"if all values in "Issue" are the same AND all values in "Level" are the same then return sum of "Amount" in "Adjusted Amount"- OR if all values in "Issue" are the same BUT "Level" is unique then return "Amount" in "Adjusted Amount".
Example result:
| Issue | level | Amount | Adjusted Amount |
| ABCD | 3 | 4995 | 5837 |
| ABCD | 2 | 7 | 7 |
| ABCD | 3 | 842 | 5837 |
| WXYZ | 3 | 3000 | 3000 |
| WXYZ | 4 | 500 | 500 |
1 Reply
- I don't think that will work.
The first part is easy:
let dt = datatable
(Issue:string, level:int, Amount:int,Adjusted_Amount :int) [
"ABCD",3,4995,5837,
"ABCD",2,7,7,
"ABCD",3,842,5837,
"WXYZ",3,3000,3000,
"WXYZ",4,500,500,
];
dt
| summarize anz = count(), Adjusted_Amount = sum(Amount) by Issue, level
| where anz > 1
the problem with your "OR"-clause is that you want to search for single values (Amount) and at the same time you are trying to group by Issue. So OR would be
Issue level
ABCD 2
WXYZ 3
WXYZ 4
what would be the outcome of Adjusted Amount for WXYZ level 3 ? 4995 ? 842 ? 4995 + 842 ?
