Dear Qlikviewers,
I wish I could get any help there. I have source data looking like:
| Ticket_Number | Change_DateTime | Changed_Field | New_Value |
| 240026 | 2015-08-31 09:48:55 | Responsible | Contact Center |
| 240026 | 2015-08-31 09:48:55 | State | 01.Registered |
| 240026 | 2015-09-02 17:47:35 | Due Date | 9/10/2015 10:48:55 AM |
| 240026 | 2015-09-02 17:47:35 | Responsible | Gadyrshin, Albert PH/RU |
| 240026 | 2015-09-02 17:47:35 | State | 08.Work In Progress |
| 240026 | 2015-09-02 17:47:36 | Priority | 04 Low |
| 240026 | 2016-03-04 10:48:56 | Responsible | Toropchin, Maksim PH/RU |
| 240026 | 2016-03-14 14:27:41 | Responsible | RIM-T1-Service desk |
| 240026 | 2016-03-15 14:31:16 | Responsible | Gerbeleva, Yuliya PH/RU/EXT |
| 240026 | 2016-03-15 14:31:16 | State | 14.Done, confirming closure. |
| 240026 | 2016-03-18 15:41:08 | Due Date | 9/15/2015 11:58:47 AM |
| 240026 | 2016-03-18 15:41:08 | State | 15.Closed |
The actual table have about 200k strings, limited during load.
What I need is to have the final pivot table looks like:
| Group | Date | 01/06 | 02/06 | 03/06 | 04/06 | 05/06 | 06/06 |
|---|
| RIM-T1-Service desk | Opened | 30 | 15 | 18 | 22 | 19 | 6 |
| Closed | 20 | 17 | 32 | 36 | 95 | 22 |
| Work in progress | 5 | 8 | 3 | 2 | 8 | 29 |
| Work in progress more 5 days | 1 | 2 | 3 | 15 | 21 | 18 |
The problem is that I know the date ticket came to "08.Work in Progress" state and the date when it changed it's state to "14. Done, confirming closure". How to show this ticket as "work in progress" in all the dates it was actually work in progress, and if it was more then 5 days in this state count it in the corresponding field? Current project is attached.
