topic Re: Displaying Chart based on condition in QlikView

Displaying Chart based on condition

Mon, 17 Feb 2014 13:16:16 GMT

I have file that contains several records with fields which has following example data:

Sending Application	Destination Application	DateTimeReceived	DateTimeProcessed
SAPP1	DAPP1	14-02-2014 09:41:10	14-02-2014 09:53:24
SAPP2	DAPP1	14-02-2014 09:44:12
SAPP3	DAPP2	14-02-2014 09:44:13	14-02-2014 09:57:26
SAPP1	DAPP3	14-02-2014 09:50:15	14-02-2014 10:02:27
SAPP4	DAPP3	14-02-2014 09:50:17
SAPP2	DAPP1	14-02-2014 09:56:19
SAPP3	DAPP2	14-02-2014 10:02:22
SAPP1	DAPP3	14-02-2014 10:05:22	14-02-2014 10:17:30
SAPP4	DAPP2	14-02-2014 10:05:22	14-02-2014 10:17:30
SAPP2	DAPP3	14-02-2014 10:05:23

I would like to create an Alert to display no of records by Destination Application which have not been processed within a certain process time average ie, DateTimeProcessed - CurrentDateTime > AverageProcessingTime (hh:mm:ss).

Each Destination application will have a different AverageProcessingTime.

Does anyone know how best to display an Alert including how I define/use the AverageProcessingTime by Destination Application to get the desired result. There may be instances where all records have been processed within AverageProcessingTime

Thanks

Re: Displaying Chart based on condition

Mon, 17 Feb 2014 13:23:16 GMT

hi Arif,

try this

IF(ISNull(DateTimeProcessed),Count(Destination Application))

Re: Displaying Chart based on condition

Mon, 17 Feb 2014 13:33:00 GMT

I only want the counts (by Destination Application), if the elapsed time is greater then average processing time (APT)

ie CurrentDateTime - DateTimeProcessed > AverageProcessingTime. Each APT will be different for the destination applications.

Thanks

Re: Displaying Chart based on condition

Mon, 17 Feb 2014 13:36:22 GMT

hi arif,

please give example data in qvw for better understanding

Re: Displaying Chart based on condition

Mon, 17 Feb 2014 13:40:36 GMT

Please see the sample data and see if that helps

In addition the AVP would be as follows:

DAPP1 : 3 mins

DAPP2 : 10 mins

DAPP3: 7 mins

Thanks

Re: Displaying Chart based on condition

Mon, 17 Feb 2014 13:49:03 GMT

First question, is the average processing time based on your data set of averages for each destination type or is this a predefined business rule? If the first option then do this in script

Data: //This is your main data load

Load

Autonumber(SendingApplication&DestinationApplication&DateTimeReceived) as KEY

SendingApplication,

DestinationApplication,

DateTimeReceived,

DateTimeProcessed

FROM ...

AverageProcessingTime: //This is where we will calculate the value for you and add it to your main data set

JOIN (Data)

Load

KEY

avg(DateTimeProcessed - DateTimeReceived) as AverageProcessingTime

Resident Data

WHERE not is null(DateTimeProcessed)

GROUP BY KEY;

DataFinal: //We check against the average time now to see which is greater than the average

LOAD

if(ActualProcessingTime > AverageProcessingTime,1,0) as ProcessingAlertFlag; //If larger then average flag as 1

LOAD

DateTimeProcessed - DateTimeReceived as ActualProcessingTime

Resident Data;

Drop Table Data;

In your expression you now count(if(ProcessingAlertFlag = 1,ProcessingAlertFlag)) and can have your sending and destination fields as dimensions

Not sure if this is what you want, please elaborate more on how the average processing time is fed into your model and we can then come up with a solution

Cheers,

Byron

Re: Displaying Chart based on condition

Mon, 17 Feb 2014 13:54:46 GMT

In response to your reply

AverageProcessingTime:

MAPPING LOAD * INLINE [

DestinationApplication, TimeTarget

DAPP1, 3

DAPP2, 10

DAPP3, 7

];

Data: //This is your main data load

Load

if(TimeProcessed > TimeTarget,1,0) as ProcessedFlag;

Load

Autonumber(SendingApplication&DestinationApplication&DateTimeReceived) as KEY

SendingApplication,

DestinationApplication,

applymap('AverageProcessingTime',DestinationApplication) as TimeTarget

(DateTimeReceived - DateTimeProcessed)*24/60 as TimeProcessed //To create a minute version of your time

DateTimeReceived,

DateTimeProcessed

FROM ...