Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have some ticketing data that looks like this:
ticket number | history_id | action |
2019031310000010 | 658617 | other |
2019031310000010 | 658618 | other |
2019031310000010 | 658631 | other |
2019031310000010 | 658632 | other |
2019031310000010 | 658633 | other |
2019031310000010 | 658634 | CLOSE |
2019031310000010 | 658635 | other |
2019031310000010 | 658636 | other |
2019031310000010 | 658639 | other |
2019031310000010 | 658640 | other |
2019031310000010 | 658641 | other |
2019031310000010 | 658642 | CLOSE |
2019031310000010 | 658643 | other |
2019031310000010 | 658644 | other |
2019031310000010 | 658645 | other |
2019031310000010 | 658646 | other |
2019031310000010 | 658710 | other |
2019031310000010 | 658880 | CLOSE |
2019031310000010 | 658881 | other |
2019031310000010 | 658892 | other |
2019031310000010 | 658895 | other |
2019031310000010 | 658902 | CLOSE |
I have many logged events for the same ticket and some of them mean that the ticket was closed (flagged CLOSE).
For reporting purposes I must relate all logged events with the closest next closing one.
I tried using peek(), rowno() and other resources to create that index but couldn't find the way to get to this:
ticket number | history_id | action | subticket number |
2019031310000010 | 658617 | other | 658634 |
2019031310000010 | 658618 | other | 658634 |
2019031310000010 | 658631 | other | 658634 |
2019031310000010 | 658632 | other | 658634 |
2019031310000010 | 658633 | other | 658634 |
2019031310000010 | 658634 | CLOSE | 658634 |
2019031310000010 | 658635 | other | 658642 |
2019031310000010 | 658636 | other | 658642 |
2019031310000010 | 658639 | other | 658642 |
2019031310000010 | 658640 | other | 658642 |
2019031310000010 | 658641 | other | 658642 |
2019031310000010 | 658642 | CLOSE | 658642 |
2019031310000010 | 658643 | other | 658880 |
2019031310000010 | 658644 | other | 658880 |
2019031310000010 | 658645 | other | 658880 |
2019031310000010 | 658646 | other | 658880 |
2019031310000010 | 658710 | other | 658880 |
2019031310000010 | 658880 | CLOSE | 658880 |
2019031310000010 | 658881 | other | 658902 |
2019031310000010 | 658892 | other | 658902 |
2019031310000010 | 658895 | other | 658902 |
2019031310000010 | 658902 | CLOSE | 658902 |
If only the was a way to read and update a variable inside a LOAD statement it wolud be so easy.
Can you please help me find the way?
Thanks!
EDIT: the subticket number doesn't have to be the history_id of the closing entry.
Try this:
data:
LOAD *,if(F3='CLOSE',F2) as Row INLINE [
F1, F2, F3
2019031310000010, 658617, other
2019031310000010, 658618, other
2019031310000010, 658631, other
2019031310000010, 658632, other
2019031310000010, 658633, other
2019031310000010, 658634, CLOSE
2019031310000010, 658635, other
2019031310000010, 658636, other
2019031310000010, 658639, other
2019031310000010, 658640, other
2019031310000010, 658641, other
2019031310000010, 658642, CLOSE
2019031310000010, 658643, other
2019031310000010, 658644, other
2019031310000010, 658645, other
2019031310000010, 658646, other
2019031310000010, 658710, other
2019031310000010, 658880, CLOSE
2019031310000010, 658881, other
2019031310000010, 658892, other
2019031310000010, 658895, other
2019031310000010, 658902, CLOSE
];
data2:
NoConcatenate
//load F1,F2,F3,if(F3='CLOSE',null(),MinRow) as MinRow;
load
F1,F2,F3,
nummin(Row, peek( 'CloseRow')) as CloseRow
Resident data
order by F2 desc;
drop table data;
Hi!
Thank you very much for your effort but it's not working since there's gaps in the F2 column.
hello, what gaps? can you post error by photo?
its working for me, you can look at metod and repeat it on your data.
build qvw for you )
Hi,
Theres gaps in history_id (from 658636 to 658639), and the missing id's belong to another ticket that may have CLOSE action. I'm trying to fix it changing the order of the load.
Thanks!