Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Daily Ticket Stock

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. 

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

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);

 

Capture.PNG

Best Regards,
KC

View solution in original post

9 Replies
Highlighted
Master II
Master II

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.

Best Regards,
KC
Highlighted
Contributor III
Contributor III

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 IDCreate DayComplete Day Day#of Open TicketsNote
T11-Nov-18  1-Nov-181T1
T25-Nov-1815-Nov-18 2-Nov-181T1
T33-Nov-186-Nov-18 3-Nov-183T1,T3,T5
T410-Nov-1812-Nov-18 4-Nov-183T1,T3,T5
T53-Nov-18  5-Nov-184T1,T2,T3,T5
    6-Nov-183T1,T2,T5
    7-Nov-183T1,T2,T5
    8-Nov-183T1,T2,T5
    9-Nov-183T1,T2,T5
    10-Nov-184T1,T2,T4,T5
    11-Nov-184T1,T2,T4,T5
    12-Nov-183T1,T2,T5
    ….….. 
Highlighted
Contributor III
Contributor III

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,

Highlighted
Master II
Master II

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);

 

Capture.PNG

Best Regards,
KC

View solution in original post

Highlighted
Master II
Master II

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

for 2nd expression.

Best Regards,
KC
Highlighted
Contributor III
Contributor III

Thanks a lot for the solution. Its working perfect. 

Highlighted
Master II
Master II

You are welcome 🙂

Best Regards,
KC
Highlighted
Contributor III
Contributor III

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?

Highlighted
Master II
Master II

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)

 

 

Best Regards,
KC