Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Analysis of Scheduled Tasks Help

Hi,

I have an excel with 2 tabs:

1) Scheduled Task definitions data

2) Scheduled Tasks run time info (14 Months) ~ 248K rows.

My goal is to make full analysis.

Please help me with the following:

I have execution date field (EXEC_TIME) of type time stamp (01-02-2015  10:34:53 AM) - I want to split it to be generic days and months:

Give me all Mondays, Sundays etc.., Give me all January, February etc..

So for example I want all the Mondays with their run times (Generic day): Mon 1232:44:22 (HHHH:MMSmiley FrustratedS).

Or Monthly run time: January: 10000:11:33.

NOTE: The main problem I face is that I have a lot of Mondays, Tuesdays etc...

I need a solution like floor in order to get rid of repetitions. Day names and months should be distinct.

Finally I would like to show the run time for all Sundays, Mondays etc...

Same with months.

Here is my script so far:

[Scheduled Task Run Time Data]:

LOAD

    TASK_ID,

    EXEC_TIME,

    END_TIME,

   

    Date(floor(EXEC_TIME)) as EXEC_DATE,

    Date(floor(EXEC_TIME), 'WWW') as EXEC_DAY,

    Date(floor(EXEC_TIME), 'MMM') as EXEC_MON,

    END_TIME - EXEC_TIME as RUN_TIME,

    COMMENTS as RUN_TIME_COMMENTS,

    TASK_EXEC_USER,

    PARENT_ID,

    TRIGGER_NAME,

    EXEC_STATUS

FROM [lib://Materials for Work/ScheduledTasksAnalysisStage3.xlsx]

(ooxml, embedded labels, table is ALLRunTime);

Please help me what should I right in the script.

Thanks in advance!

Steve

Community Browser