Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
Hi Vchuprina,
Thanks for given the solution.
Regards,
Ram