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

[paulista]

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 

[Taoufiq_Zarra · Accepted solution]

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] , ...

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:

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

Cheers,

[paulista]

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

[Taoufiq_Zarra]

Generic Load