I am looking for a solution to extract rows containing certain keywords from column "X". and the remaining data will add to "Total". For example any keyword with SI or SB will be added to count field "Log" and the other entries excluding empty cell will be added to count field "Total".
SNO | X |
1 | 400 |
2 | SI-SCRIPT-ERROR |
3 | (SI-BPR-01) |
4 | SB-Timeout |
5 | SB-OrderFound |
6 | (SB-BPR-02)--(SB-EXL-001) |
7 | 201 |
8 | SI-RAS-200 |
9 | <empty> |
Something like
| where match(X, "\b(SI|SB)\b")
| stats sum(SNO) as SNO_total
Here is a full emulation
| makeresults format=csv data="SNO, X
1,400
2, SI-SCRIPT-ERROR
3, (SI-BPR-01)
4, SB-Timeout
5, SB-OrderFound
6, (SB-BPR-02)--(SB-EXL-001)
7, 201
8, SI-RAS-200
9,"
``` data emulation above ```
| where match(X, "\b(SI|SB)\b")
| stats sum(SNO) as SNO_total
Result is
SNO_total |
28 |
Something like
| where match(X, "\b(SI|SB)\b")
| stats sum(SNO) as SNO_total
Here is a full emulation
| makeresults format=csv data="SNO, X
1,400
2, SI-SCRIPT-ERROR
3, (SI-BPR-01)
4, SB-Timeout
5, SB-OrderFound
6, (SB-BPR-02)--(SB-EXL-001)
7, 201
8, SI-RAS-200
9,"
``` data emulation above ```
| where match(X, "\b(SI|SB)\b")
| stats sum(SNO) as SNO_total
Result is
SNO_total |
28 |