Smart data aggregation

Thu, 16 Jun 2016 06:56:22 GMT

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.

topic Smart data aggregation in QlikView

Smart data aggregation