Displaying Chart based on condition

[#1]

Hi

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

[#2]

hi Arif,

try this

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

[#3]

Hi

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

[#4]

hi arif,

please give example data in qvw for better understanding

[#5]

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

[#6]

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

[#7]

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 ...