topic Re-open ticket in QlikView

Re-open ticket

0li5a3a — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I would like to count the number of tickets I have re-open this month.

In my scenario this is the status logic : Assigned - Work In progress - Cleared( from this stage we can move the ticket back to Assigned or Work In progress) - Closed.

I have two tables which one contain the current status of the tickets and one is the history status of the tickets.

Order:

LOAD * INLINE [

id, Status, OrderDate

1, Closed, 20/08/2018 08:00

2, Closed, 20/08/2018 08:00

3, Cleared, 20/08/2018 08:00

4, Assigned, 20/08/2018 09:00

5, Work In Progress, 20/08/2018 08:00

];

left join(Order)

StatusHistory:

LOAD * INLINE [

id, Status History, Timestamp

1, Assigned, 20/08/2018 08:00

1, Work In Progress, 20/08/2018 08:05

1, Cleared, 20/08/2018 08:55

1, Closed, 20/08/2018 09:20

2, Assigned, 20/08/2018 08:00

2, Work In Progress, 20/08/2018 08:05

2, Cleared, 20/08/2018 08:55

2, Work In Progress, 20/08/2018 09:20

2, Cleared, 20/08/2018 10:10

2, Closed, 20/08/2018 10:20

3, Assigned, 20/08/2018 08:00

3, Work In Progress, 20/08/2018 08:05

3, Cleared, 20/08/2018 08:55

3, Closed, 20/08/2018 09:20

4, Assigned, 20/08/2018 09:00

5, Assigned, 20/08/2018 08:00

5, Work In Progress, 20/08/2018 08:00

];

Could someone how count the number of re-open tickets?

Regards,

Re: Re-open ticket

maren_amthor — Tue, 21 Aug 2018 15:39:14 GMT

You can try counting everytime a ticket is more than once in status "Work in Progress".

count({<Status={"Work In Progress"}>} id) - count(Distinct {<Status={"Work In Progress"}>} id)

Re: Re-open ticket

vishsaggi — Tue, 21 Aug 2018 16:08:55 GMT

What is your expected output from your above sample data?

Re: Re-open ticket

andrey_krylov — Tue, 21 Aug 2018 18:07:50 GMT

I would flag "re-opening" in the load script, and then sum up that flag:

StatusHistory:

LOAD * INLINE [

id, Status History, Timestamp

1, Assigned, 20/08/2018 08:00

1, Work In Progress, 20/08/2018 08:05

1, Cleared, 20/08/2018 08:55

1, Closed, 20/08/2018 09:20

2, Assigned, 20/08/2018 08:00

2, Work In Progress, 20/08/2018 08:05

2, Cleared, 20/08/2018 08:55

2, Work In Progress, 20/08/2018 09:20

2, Cleared, 20/08/2018 10:10

2, Closed, 20/08/2018 10:20

3, Assigned, 20/08/2018 08:00

3, Work In Progress, 20/08/2018 08:05

3, Cleared, 20/08/2018 08:55

3, Closed, 20/08/2018 09:20

4, Assigned, 20/08/2018 09:00

5, Assigned, 20/08/2018 08:00

5, Work In Progress, 20/08/2018 08:00

];

Left Join

LOAD *, If(id = Previous(id) and Previous([Status History]) = 'Cleared' and [Status History] <> 'Closed', 1, 0) as [Re-Opened]

Resident StatusHistory Order By id, Timestamp;

Re: Re-open ticket

0li5a3a — Thu, 23 Aug 2018 13:21:30 GMT

Thanks much appreciate!