I've a table like below and I want to merge two rows based on the COMMONID
1. JBID JOBTYPE START_TIME END_TIME COMMONID
2. 2020-03-10T06:30:00 2020-03-10T08:30:00 abc
3. 6398 Medium abc
4. 5649 Medium def
5. 2020-03-10T08:30:00 2020-03-10T10:30:00 def
6. 5649 Medium ghi
7. 2020-03-20T08:30:00 2020-03-20T10:30:00 ghi
8. 2020-03-11T08:30:00 2020-03-11T10:30:00 jkl
9. 6383 Medium jkl
10. 7070 Medium mno
11. 2020-03-10T08:30:00 2020-03-10T10:30:00 mno
12. 11690 Medium pqr
13. 2020-03-12T06:30:00 2020-03-12T08:30:00 pqr
14. 2020-03-19T06:30:00 2020-03-19T08:30:00 stu
15. 6398 Medium stu
16. 6398 Medium vwx
17. 2020-03-10T06:30:00 2020-03-10T08:30:00 vwx
The resulting table should look like below
1. JBID JOBTYPE START_TIME END_TIME COMMONID
2. 6398 Medium 2020-03-10T06:30:00 2020-03-10T08:30:00 abc
3. 5649 Medium 2020-03-10T08:30:00 2020-03-10T10:30:00 def
4. 5649 Medium 2020-03-20T08:30:00 2020-03-20T10:30:00 ghi
5. 6383 Medium 2020-03-11T08:30:00 2020-03-11T10:30:00 jkl
6. 7070 Medium 2020-03-10T08:30:00 2020-03-10T10:30:00 mno
7. 11690 Medium 2020-03-12T06:30:00 2020-03-12T08:30:00 pqr
8. 6398 Medium 2020-03-19T06:30:00 2020-03-19T08:30:00 stu
9. 6398 Medium 2020-03-10T06:30:00 2020-03-10T08:30:00 vwx
How do I achieve this?
hi @charan986
You can do this using stats command:
<base search>| stats first(*) as * by COMMONID
I typically do this:
... | stats list(*) AS * BY COMMONID
Just in case there are some duplicated lines.
....
| selfjoin COMMONID
hi @charan986
You can do this using stats command:
<base search>| stats first(*) as * by COMMONID
Thanks, it worked 🙂