Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
rathorep
Contributor III
Contributor III
‎2020-11-09 03:46 AM

Calculate Task which got failed in last 5 days

Hello Friends,

Below is my data set, I need to have only task which failed during last 5 days continuously.

For example- App name- Hospitality, is getting failed from 2nd NOV to 6th NOV 2020, so i only want this app with count 5 days in separate column.

[App Name][Task Name][Reload Status]Last Reload Failure
HospitalityReload task of HospitalityFinishedFail2020-10-30 19:01:48
HospitalityReload task of HospitalityFinishedFail2020-11-02 04:57:46
HospitalityReload task of HospitalityFinishedFail2020-11-03 12:38:58
HospitalityReload task of HospitalityFinishedFail2020-11-03 12:47:27
HospitalityReload task of HospitalityFinishedFail2020-11-04 05:33:08
HospitalityReload task of HospitalityFinishedFail2020-11-04 08:00:15
HospitalityReload task of HospitalityFinishedFail2020-11-04 16:38:11
HospitalityReload task of HospitalityFinishedFail2020-11-05 08:28:51
HospitalityReload task of HospitalityFinishedFail2020-11-06 08:51:29
HospitalityReload task of HospitalityFinishedFail2020-11-06 14:33:20
HospitalityReload task of HospitalityFinishedFail2020-11-06 19:02:04
QLIK PLATFORMReload task of QLIK PLATFORMFinishedFail2019-11-15 04:25:13
QLIK PLATFORMReload task of QLIK PLATFORMFinishedFail2019-11-25 22:00:52
QLIK PLATFORMReload task of QLIK PLATFORMFinishedFail2020-02-02 01:00:35
QLIK PLATFORMReload task of QLIK PLATFORMFinishedFail2020-02-09 01:00:35
QLIK PLATFORMReload task of QLIK PLATFORMFinishedFail2020-02-15 22:00:53

 

 

Desired Result   
[App Name][Task Name][Reload Status]Count
HospitalityReload task of HospitalityFinishedFail5

 

Thanks

1,202 Views
0 Likes
5 Replies
Taoufiq_Zarra
MVP
MVP
‎2020-11-09 04:25 AM

@rathorep  One Solution:

Data:


LOAD [App Name],[Task Name],[Reload Status],Date(floor(Timestamp#([Last Reload Failure],'YYYY-MM-DD hh:mm:ss'))) as [Last Reload Failure] INLINE [
    App Name, Task Name, Reload Status, Last Reload Failure
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-10-30 19:01:48
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-02 04:57:46
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-03 12:38:58
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-03 12:47:27
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-04 05:33:08
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-04 08:00:15
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-04 16:38:11
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-05 08:28:51
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-06 08:51:29
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-06 14:33:20
    Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-06 19:02:04
    QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2019-11-15 04:25:13
    QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2019-11-25 22:00:52
    QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2020-02-02 01:00:35
    QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2020-02-09 01:00:35
    QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2020-02-15 22:00:5
];

Tmp:
noconcatenate
load  [App Name],[Task Name],[Reload Status],[Last Reload Failure] resident Data order by [App Name],[Task Name],[Reload Status],[Last Reload Failure] ;

drop table Data;


Tmp2:
noconcatenate
load *,if([App Name]=peek([App Name]) and [Task Name]=peek([Task Name]) and  [Reload Status]=peek([Reload Status]), if([Last Reload Failure]=peek([Last Reload Failure])+1,peek(flag)+1, if([Last Reload Failure]=peek([Last Reload Failure]) ,peek(flag),0))) as flag resident Tmp;
drop table Tmp;


output:
noconcatenate

load * where Count=5;
load distinct [App Name],[Task Name],[Reload Status],count(distinct flag) as Count resident Tmp2 group by [App Name],[Task Name],[Reload Status];

drop table Tmp2;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
1,186 Views
0 Likes
rathorep
Contributor III
Contributor III
‎2020-11-09 05:45 AM
Author

In response to Taoufiq_Zarra

I want to do it in front end.

1,171 Views
0 Likes
rathorep
Contributor III
Contributor III
‎2020-11-17 05:18 AM
Author

is this can be done in front end plz?

1,139 Views
0 Likes
MayilVahanan
MVP
MVP
‎2020-11-17 06:10 AM

In response to rathorep

Hi @rathorep 

Front end, try like below


LOAD *, Date(Floor(LastReloadFailure)) as LastReloadFailureDate INLINE [
AppName, TaskName, ReloadStatus, LastReloadFailure
Hospitality, Reload task of Hospitality, FinishedFail, 2020-10-30 19:01:48
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-02 04:57:46
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-03 12:38:58
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-03 12:47:27
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-04 05:33:08
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-04 08:00:15
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-04 16:38:11
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-05 08:28:51
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-06 08:51:29
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-06 14:33:20
Hospitality, Reload task of Hospitality, FinishedFail, 2020-11-06 19:02:04
QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2019-11-15 04:25:13
QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2019-11-25 22:00:52
QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2020-02-02 01:00:35
QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2020-02-09 01:00:35
QLIK PLATFORM, Reload task of QLIK PLATFORM, FinishedFail, 2020-02-15 22:00:53
];

 

If(Sum(Aggr(If(Above(ReloadStatus) = ReloadStatus and Date(Above(LastReloadFailureDate)+1)=Date(LastReloadFailureDate), 1, 0),AppName, TaskName, LastReloadFailureDate)) >= 4,
Sum(Aggr(If(Above(ReloadStatus) = ReloadStatus and Date(Above(LastReloadFailureDate)+1)=Date(LastReloadFailureDate), 1, 0),AppName, TaskName, LastReloadFailureDate))+1)

Screenshot:

MayilVahanan_0-1605611380311.png

P.S: Try in backend instead of front end for better performance.

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
1,135 Views
0 Likes
rathorep
Contributor III
Contributor III
‎2020-11-30 03:45 AM
Author

In response to MayilVahanan

I have used below expressions as I don't have direct column- "LastReloadFailureDate"

 "LastReloadFailureDate": (timestamp(Max({<[Reload Failure]={1}>} LogTimeStamp)))

Expression I have used:

If(Sum(Aggr(If(Above([Reload Status]) = 'FinishedFail'
and Date(Above((timestamp(Max({<[Reload Failure]={1}>} LogTimeStamp))))+1)=Date((timestamp(Max({<[Reload Failure]={1}>} LogTimeStamp)))), 1, 0)
,[App Name], [Task Name], (timestamp(Max({<[Reload Failure]={1}>} LogTimeStamp))))) >= 4,
Sum(Aggr(If(Above([Reload Status]) = 'FinishedFail' and Date(Above((timestamp(Max({<[Reload Failure]={1}>} LogTimeStamp))))+1)=Date((timestamp(Max({<[Reload Failure]={1}>} LogTimeStamp)))),
1, 0),[App Name], [Task Name],
(timestamp(Max({<[Reload Failure]={1}>} LogTimeStamp)))))+1)

 

but I am getting "-" only in column. Request you  to please help where i am writing wrong code.

Thanks!

1,109 Views
0 Likes