Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

i want to find the maximum and minimum time for each ID , time taken for each id and average time

I am new to Qlikview developing and I am turning to you for some help 

Data:

Contains 3 columns id, process stages and timestamp

ID PROCESS STAGES TIMESTAMP
1 batch submitted 29/11/2019 15:17:35
1 batch accepted 29/11/2019 15:18:55
1 batch completed 29/11/2019 15:24:59
2 started 02/12/2019 13:47:59
2 reviewCaseClaimed 02/12/2019 13:48:05
2 reviewCaseCompleted REJECTED 02/12/2019 14:11:39
2 invalidCase notifyTo 02/12/2019 14:11:41
3 started 02/12/2019 09:55:34
3 reviewCaseClaimed 02/12/2019 09:55:53
3 reviewCaseCompleted APPROVED 02/12/2019 09:56:05
3 request submitted 02/12/2019 09:56:07
3 returnTo 02/12/2019 10:58:27

 

i want to find the maximum and minimum time for each ID , time taken for each id and average time taken.

code:

 


LOAD

ID,
processstages,
TIMESTAMP,
max(timestamp(TIMESTAMP, 'DD/MM/YYYY hh:mm:ss')) as maxtime,
max(timestamp(TIMESTAMP, 'DD/MM/YYYY hh:mm:ss')) as mintime,
maxtime-mintime
from dataset

 GROUP BY ID;

 

Here I tried to get it working but failed. I tried to find the problem with this and tried some solutions given in discussions here, but couldn't make it work, would be great if someone could help!

thanks 

 

 

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: i want to find the maximum and minimum time for each ID , time taken for each id and average tim

Hi,

Welcome to Qlik !

first of all if I just look at the syntax, the names of the loaded fields do not exist in the data source.
for example "processes" to be replaced by[PROCESS TRAININGS].
then we don't do maxtime-mintime directly, the Qlik editor doesn't recognize these fields yet, rather choose load from "Resident".
and finally there is also a conceptual error at the Group By level.
🙂
otherwise, after a quick reading of your case, I propose the following script <Generic Load>:

 

InputTable:
LOAD * INLINE [
    ID, PROCESS STAGES, TIMESTAMP
    1,  batch submitted, 29/11/2019 15:17:35
    1,  batch accepted, 29/11/2019 15:18:55
    1,  batch completed, 29/11/2019 15:24:59
    2,  started    , 02/12/2019 13:47:59
    2,  reviewCaseClaimed, 02/12/2019 13:48:05
    2,  reviewCaseCompleted REJECTED, 02/12/2019 14:11:39
    2,  invalidCase notifyTo  , 02/12/2019 14:11:41
    3,  started    , 02/12/2019 09:55:34
    3,  reviewCaseClaimed, 02/12/2019 09:55:53
    3,  reviewCaseCompleted APPROVED, 02/12/2019 09:56:05
    3,  request submitted, 02/12/2019 09:56:07
    3,  returnTo , 02/12/2019 10:58:27
];

GenTable:
Generic Load ID, [PROCESS STAGES], timestamp(TIMESTAMP, 'DD/MM/YYYY hh:mm:ss') as TIMESTAMP Resident InputTable;

ResultTable:
LOAD Distinct ID Resident InputTable;

FOR i = 0 to NoOfTables()
  TableList:
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1
  WHERE WildMatch(TableName($(i)), 'GenTable.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
  LET vTable = FieldValue('Tablename', $(i));
  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
NEXT i

 

With this script you can propose these visualizations :

1-Convert the rows into columns, with this visualization, you can display for each ID its life cycle with dates.
for that you make a simple table with the fields: ID,[batch submited] , ...

Capture.PNG

2-

Table Min Max:
dimension : ID
measures:
1-Min Time

=timestamp(Min(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss')

2-Max Time

=timestamp(Max(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss')

3- Max-Min

=timestamp(timestamp(Max(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss')-timestamp(Min(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')

 

Results:

Capture.PNG

 

bonus, you can add the FirstSortedValue function to display the status of the Max and min

Capture.PNG

 

Cheers,

Regards,
Taoufiq ZARRA

View solution in original post

3 Replies
Highlighted
Specialist III
Specialist III

Re: i want to find the maximum and minimum time for each ID , time taken for each id and average tim

Hi,

Welcome to Qlik !

first of all if I just look at the syntax, the names of the loaded fields do not exist in the data source.
for example "processes" to be replaced by[PROCESS TRAININGS].
then we don't do maxtime-mintime directly, the Qlik editor doesn't recognize these fields yet, rather choose load from "Resident".
and finally there is also a conceptual error at the Group By level.
🙂
otherwise, after a quick reading of your case, I propose the following script <Generic Load>:

 

InputTable:
LOAD * INLINE [
    ID, PROCESS STAGES, TIMESTAMP
    1,  batch submitted, 29/11/2019 15:17:35
    1,  batch accepted, 29/11/2019 15:18:55
    1,  batch completed, 29/11/2019 15:24:59
    2,  started    , 02/12/2019 13:47:59
    2,  reviewCaseClaimed, 02/12/2019 13:48:05
    2,  reviewCaseCompleted REJECTED, 02/12/2019 14:11:39
    2,  invalidCase notifyTo  , 02/12/2019 14:11:41
    3,  started    , 02/12/2019 09:55:34
    3,  reviewCaseClaimed, 02/12/2019 09:55:53
    3,  reviewCaseCompleted APPROVED, 02/12/2019 09:56:05
    3,  request submitted, 02/12/2019 09:56:07
    3,  returnTo , 02/12/2019 10:58:27
];

GenTable:
Generic Load ID, [PROCESS STAGES], timestamp(TIMESTAMP, 'DD/MM/YYYY hh:mm:ss') as TIMESTAMP Resident InputTable;

ResultTable:
LOAD Distinct ID Resident InputTable;

FOR i = 0 to NoOfTables()
  TableList:
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1
  WHERE WildMatch(TableName($(i)), 'GenTable.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
  LET vTable = FieldValue('Tablename', $(i));
  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
NEXT i

 

With this script you can propose these visualizations :

1-Convert the rows into columns, with this visualization, you can display for each ID its life cycle with dates.
for that you make a simple table with the fields: ID,[batch submited] , ...

Capture.PNG

2-

Table Min Max:
dimension : ID
measures:
1-Min Time

=timestamp(Min(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss')

2-Max Time

=timestamp(Max(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss')

3- Max-Min

=timestamp(timestamp(Max(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss')-timestamp(Min(TIMESTAMP), 'DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')

 

Results:

Capture.PNG

 

bonus, you can add the FirstSortedValue function to display the status of the Max and min

Capture.PNG

 

Cheers,

Regards,
Taoufiq ZARRA

View solution in original post

Highlighted
Contributor III
Contributor III

Re: i want to find the maximum and minimum time for each ID , time taken for each id and average tim

thanks for the solution, bonus was also quite helpful.  Can you explain how it works please.

Highlighted
Specialist III
Specialist III

Re: i want to find the maximum and minimum time for each ID , time taken for each id and average tim

Generic Load

Regards,
Taoufiq ZARRA