Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ramu_i
Contributor III
Contributor III

Task Start date and end date to find the task status

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

Labels (4)
1 Solution

Accepted Solutions
vchuprina
Specialist
Specialist

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:

vchuprina_1-1653041682457.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").

View solution in original post

4 Replies
vchuprina
Specialist
Specialist

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:

vchuprina_0-1652438792492.png

Regards,

Vitalii

 

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Ramu_i
Contributor III
Contributor III
Author

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

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:

vchuprina_1-1653041682457.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Ramu_i
Contributor III
Contributor III
Author

Hi  Vchuprina,

Thanks for given the solution.

Regards,

Ram