topic Re: Daily Ticket Stock in QlikView

Daily Ticket Stock

vesiletaskiran — Tue, 27 Nov 2018 11:10:04 GMT

I have model with 3 fields.

Ticket Id , Create Day, Complete Day.

I need to count daily open tickets. So the output will be

Day , #of open Tickets. (Create Day>=Day and (Complete Day<Day or Complete Day is Null)

I have tried for each ticket creating a record with while loop to list open days . And aggregating this after daily. This solution created millions of records because of my high data volume

LOAD
TicketID,
(create_day+iterno()-1) as OpenDate
resident Data3PP
WHILE create_day<complete_day;

Would you recommend an optimal solution for that?

Thanks in advance.

Re: Daily Ticket Stock

jyothish8807 — Tue, 27 Nov 2018 11:26:08 GMT

Hi vesiletaskiran,

Can you please share a sample data for reference ? Also what od you mean by 'Day' ? Is it today's date ?

You can get a list of open tickets by fetching count of tickets which do not have a complete Day.

Re: Daily Ticket Stock

vesiletaskiran — Tue, 27 Nov 2018 11:36:59 GMT

Sorry for the missing information,

I need the count for every calendar day. Every calendar day count ticket which were created and not completed.

Below is a sample data with output

Data			Output
Ticket ID	Create Day	Complete Day	Day	#of Open Tickets	Note
T1	1-Nov-18		1-Nov-18	1	T1
T2	5-Nov-18	15-Nov-18	2-Nov-18	1	T1
T3	3-Nov-18	6-Nov-18	3-Nov-18	3	T1,T3,T5
T4	10-Nov-18	12-Nov-18	4-Nov-18	3	T1,T3,T5
T5	3-Nov-18		5-Nov-18	4	T1,T2,T3,T5
			6-Nov-18	3	T1,T2,T5
			7-Nov-18	3	T1,T2,T5
			8-Nov-18	3	T1,T2,T5
			9-Nov-18	3	T1,T2,T5
			10-Nov-18	4	T1,T2,T4,T5
			11-Nov-18	4	T1,T2,T4,T5
			12-Nov-18	3	T1,T2,T5
			….	…..

Re: Daily Ticket Stock

dpduran — Tue, 27 Nov 2018 11:41:43 GMT

Hi @vesiletaskiran

I assume you want the count of days a ticket's been opened...

use the function Interval between complete and Create Dates.... if there is no Complete date then you can use Today's date to calculate days.

In the model I would flag those still open or not checking the CompleteDay fieldgenerating a field called Status.

if(LEN(CompleteDay)>1, 'CLOSED', 'OPEN') as Status

Then with a precedent load you can calculate the aging field which will give you the count of days.

if(Status='CLOSED', interval([Complete Day]-[Create Day], 'D'), interval(Today()-[Create Day], 'D') as Aging

That will give you 2 new columns, 1 with status, another one with the count of days it was or still is open.

Kind regards,

Re: Daily Ticket Stock

jyothish8807 — Tue, 27 Nov 2018 12:22:48 GMT

Hi,

Try this:

count(if([Create Day]<=Day and (len([Complete Day])=0 or [Complete Day]> Day),[Ticket ID]))

Also refer the script.

A:
Load
[Ticket ID] as [Ticket ID],
date(date#([Create Day],'DD-MMM-YY'),'DD-MMM-YY') as [Create Day],
date(date#([Complete Day],'DD-MMM-YY'),'DD-MMM-YY') as [Complete Day];
LOAD * INLINE [
Ticket ID, Create Day, Complete Day
T1, 1-Nov-18,
T2, 5-Nov-18, 15-Nov-18
T3, 3-Nov-18, 6-Nov-18
T4, 10-Nov-18, 12-Nov-18
T5, 3-Nov-18,
];

MaxMin:
Load
min([Create Day]) as MinDate,
max([Create Day]) as MaxDate
resident A;

Let vMindate=num(peek('MinDate'));
Let vMaxdate=num(peek('MaxDate'));

Temp_Calendar:
Load
$(vMindate) +IterNo()-1 as Num,
date($(vMindate) +IterNo()-1,'DD-MMM-YY') as Day
AutoGenerate 1
While $(vMindate) +IterNo()-1 <=$(vMaxdate);

Re: Daily Ticket Stock

jyothish8807 — Tue, 27 Nov 2018 12:28:23 GMT

Concat(if([Create Day]<=Day and (len([Complete Day])=0 or [Complete Day]> Day),[Ticket ID]),',')

for 2nd expression.

Re: Daily Ticket Stock

vesiletaskiran — Tue, 27 Nov 2018 13:04:17 GMT

Thanks a lot for the solution. Its working perfect.

Re: Daily Ticket Stock

jyothish8807 — Tue, 27 Nov 2018 15:06:33 GMT

You are welcome 🙂

Re: Daily Ticket Stock

vesiletaskiran — Thu, 29 Nov 2018 10:06:01 GMT

Hi Jyothish , Is there a way to calculate this on the script. on chart it takes too long to load because of high data volume?

Re: Daily Ticket Stock

jyothish8807 — Mon, 10 Dec 2018 06:18:34 GMT

Hi Vesi,

Sorry for the late reply, Please try this:

A:
Load
[Ticket ID] as [Ticket ID],
date(date#([Create Day],'DD-MMM-YY'),'DD-MMM-YY') as [Create Day],date(date#([Create Day],'DD-MMM-YY'),'DD-MMM-YY') as [Day],
date(date#([Complete Day],'DD-MMM-YY'),'DD-MMM-YY') as [Complete Day];
LOAD * INLINE [
Ticket ID, Create Day, Complete Day
T1, 1-Nov-18,
T2, 5-Nov-18, 15-Nov-18
T3, 3-Nov-18, 6-Nov-18
T4, 10-Nov-18, 12-Nov-18
T5, 3-Nov-18,
];

MaxMin:
Load
min([Create Day]) as MinDate,
max([Create Day]) as MaxDate
resident A;

Let vMindate=num(peek('MinDate'));
Let vMaxdate=num(peek('MaxDate'));

Temp_Calendar:
Load
$(vMindate) +IterNo()-1 as Num,
date($(vMindate) +IterNo()-1,'DD-MMM-YY') as Day
AutoGenerate 1
While $(vMindate) +IterNo()-1 <=$(vMaxdate);

Noconcatenate

Cal:

Load

Date

Resident Temp_Calendar;

Drop table Temp_Calendar

Left join (Cal)

Main:

Load

if([Create Day]<=Day and (len([Complete Day])=0 or [Complete Day]> Day),[Ticket ID]) as NewField

resident A;

Drop table A;

At UI try:

count(NewField)