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

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

paulista — Sat, 16 Nov 2024 19:30:22 GMT

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

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

Taoufiq_Zarra — Fri, 06 Dec 2019 17:02:50 GMT

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

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,

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

paulista — Sun, 08 Dec 2019 13:46:12 GMT

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

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

Taoufiq_Zarra — Sun, 08 Dec 2019 17:34:46 GMT

Generic Load