Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi Arif,
try this
IF(ISNull(DateTimeProcessed),Count(Destination Application))
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
hi arif,
please give example data in qvw for better understanding
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
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
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 ...