Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Certification Exam Sale - 40% off. Register by June 30, 2020. GET DETAILS
Not applicable

Issues with TimeMeasurement

Hello,

It is my first time using this forum and I hope that my question will be clear enough.

As you can see on the table I have joined, I have 4 fields :

-Date

-Time

-Rack

-Reference

I have already managed to create a timestamp with my entries using the following:

Timestamp#(Date(Date)&' '& Time(Time),'MM/DD/YYYY hh:mm') as TMSTP

What I want to calculate is a duration for each reference : this duration should be the difference between the 1st time the reference is registered in the table and the 1st time the rack field is modified. As you can see on the attached table, all references are entered at first in a rack starting with 'A999**' Then the rack is modified and gets a different kind of number.
TO calculate this duration I have tried the following but it did not work :

Interval#(min({<Location-={'A999**'}>} TMSTP) - min (TMSTP))


The min(TMSPT) will always be the 1st entry of the reference in the table and the min({<Location-={'A999**'} TMSTP) is the st time the location is changed.

Do you guys have any idea to solve this one?

Thanks you very much
I wish you all a good day

Gilbert

7 Replies
Highlighted
MVP
MVP

Re: Issues with TimeMeasurement

May be

Interval(Min(TOTAL <Reference> DateTime) - Min(DateTime),'hh:mm')

or

Interval(Min(DateTime) - Min(TOTAL <Reference> DateTime),'hh:mm')

Highlighted
Honored Contributor III

Re: Issues with TimeMeasurement

Interval#(min({<Rack-={'A999*'}>} TMSTP) - min (TMSTP))

Highlighted

Re: Issues with TimeMeasurement

Please see the attached

Highlighted
Not applicable

Re: Issues with TimeMeasurement

Thank you for your answer but I dont have access to other qlikview files...
Could you write your code in a text file please?
Thank you very much again

Highlighted

Re: Issues with TimeMeasurement

Script is below,

Data:

LOAD Date,

     timestamp(Date+Time,'DD-MM-YYYY hh:mm:ss') as TMPST,

     Time,

     Rack,

     Reference

FROM

[Inner Movements.xlsx]

(ooxml, embedded labels, table is RHECMULC797);

In front end create straight table

Dimensions:

Reference,

TMPST

Expression:

=interval(aggr(min({<Rack-={'A999*'}>}TMPST)- min(TMPST), Reference),'hh:mm:ss')

Highlighted
Honored Contributor III

Re: Issues with TimeMeasurement

try if this returns the correct timestamp

min({1<Location-={'A999*'} TMSTP)



Highlighted

Re: Issues with TimeMeasurement

Gilbert3D

are you able to resolve the issue?