Task Start date and end date to find the task status

[Ramu_i]

Hi all,

Data is there for task reloading information.

Input:

Date	Statr date	End Date	Time of reload
11-May-22	11-05-2022 02:30	11-05-2022 02:40	10 Min
12-May-22	12-05-2022 02:30	12-05-2022 02:44	14 Min
12-May-22	12-05-2022 02:30	12-05-2022 02:35	5Min
12-May-22	12-05-2022 02:30	12-05-2022 02:55	25 Min
12-May-22	12-05-2022 02:30	12-05-2022 02:42	12 Min

According to reload time it shows fast, Slow, normal

O/P:

Date	Statr date	End Date	Time of reload	Status
11-May-22	11-05-2022 02:30	11-05-2022 02:40	10 Min	Normal
12-May-22	12-05-2022 02:30	12-05-2022 02:44	14 Min	Slow
12-May-22	12-05-2022 02:30	12-05-2022 02:35	5Min	Fast
12-May-22	12-05-2022 02:30	12-05-2022 02:55	25 Min	Slow
12-May-22	12-05-2022 02:30	12-05-2022 02:42	12 Min	Fast

Thank you,

Ram

[vchuprina]

Hi,

You can use Interval Match function

Data:
LOAD
     *,
     KeepChar([Time of reload], '1234567890') as Time;
LOAD * Inline[
Date,     Start date,     End Date, Time of reload
11-May-22, 11-05-2022 02:30,    11-05-2022 02:40,    10 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:44,    14 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:35,    5 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:55,    25 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:42,    12 Min
];


Status:
LOAD * INLINE [
Start, End, Status
0, 5, Fast
6, 10, Normal
11,  , Slow
];

//Link the field Time to the time intervals defined by the fields Start and End.
Inner Join IntervalMatch ( [Time] )
LOAD Start, End
Resident Status;

Result:

Regards,

Vitalii

[Ramu_i]

Hi  Vchuprina,

It's working fine, But here Start date and end date is there based on that fine the reload time.

If we are given flag table, If yesterday reload time is 8 Min and To Day reload time 8Min 5 sec means yesterday and today status is low.

But requirement is compare with previous day reload time and find the status.

If Previous day is 8 Min and today is 9 Min - Today status shows -Slow/1 Min 00sec

If Previous day is 8 Min and today is 6 Min - Today status shows -Fast/2 Min 00 sec

and shows the time also 

Thanks 

Ram

[vchuprina · Accepted solution]

Hi, 

Try this

DATATMP:
LOAD
     Date,    
     [Start date],    
     [End Date],
     [Time of reload],
     IF(IsNull(Status) and Previous([Time of reload]) = [Time of reload], 'Normal',
          IF(IsNull(Status) and Previous([Time of reload]) > [Time of reload], 'Fast',
                IF(IsNull(Status) and Previous([Time of reload]) < [Time of reload], 'Slow', Status))) as Status;
LOAD
     Date,    
     [Start date],    
     [End Date],
     IF(RecNo()=1 ,'Normal', Null()) AS Status, // Add Normal status for the first record
     KeepChar([Time of reload], '1234567890') as [Time of reload] // Remove text to compare numbers because some of values don't have space between number and Min
     ;
LOAD * Inline[
Date,     Start date,     End Date, Time of reload
11-May-22, 11-05-2022 02:30,    11-05-2022 02:40,    10 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:44,    14 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:35,    5Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:55,    25 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:42,    12 Min
12-May-22, 12-05-2022 02:30,    12-05-2022 02:48,    12 Min
];

Result:

Regards,

Vitalii

[Ramu_i]

Hi  Vchuprina,

Thanks for given the solution.

Regards,

Ram