topic Re: Interval between two timestamp in QlikView

Interval between two timestamp

hemachandran — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to find the interval between two time stamp to the field FromTime to Inhour..

I am facing problem in the timeformat.

I need the time interval between two timestamp.

I have attached the qvd for the sample data.

Thanks in Advance...

Re: Interval between two timestamp

Anonymous — Fri, 07 Oct 2016 16:38:13 GMT

Here is an example of the Interval function :

Interval ( now() -floor(now()) , 'hh:mm:ss' )

Re: Interval between two timestamp

Anil_Babu_Samineni — Fri, 07 Oct 2016 16:39:24 GMT

Have you tried this

Interval(FromTime - Inhour) as FinalFromIn

Re: Interval between two timestamp

Anonymous — Fri, 07 Oct 2016 17:20:35 GMT

A common problem in business intelligence is when you want to link a number to a range. It could be that you have a date in one table and an interval – a “From” date and a “To” date – in another table, and you want to link the two tables. In SQL, you would probably join them using a BETWEEN clause in the comparison.

But how do you solve this in QlikView, where you should avoid joins?

The answer is to use IntervalMatch.

IntervalMatch is a prefix that can be put in front of either a Load or a SELECT statement. The Load/SELECT statement needs to contain two fields only: the “From” and the “To” fields defining the intervals. The IntervalMatch will generate all the combinations between the loaded intervals and a previously loaded numeric field.

Typically, you would first load the table with the individual numbers (The Events), then the table with the Intervals, and finally an intervalmatch that creates a third table that bridges the two first tables.

Events:
Load * From Events;

Intervals:
Load * From Intervals;

IntervalMatch:
IntervalMatch (Date)
Load distinct FromDate, ToDate resident Intervals;

The resulting data model contains three tables:

The Events table that contains exactly one record per event.
The Intervals table that contains exactly one record per interval.
The IntervalMatch table that contains exactly one record per combination of event and interval, and that links the two previous tables.

Note that this means that an event may belong to several intervals, if the intervals are overlapping. And an interval can of course have several events belonging to it.

This data model is optimal, in the sense that it is normalized and compact. All QlikView calculations operating on these tables e.g. Count(EventID) will work and will be evaluated correctly. This means that it is not necessary to join the intervalmatch table onto one of the original tables. Joining it onto another table may even cause QlikView to calculate aggregations incorrectly, since the join can change the number of records in a table.

Further, the data model contains a composite key (the FromDate and ToDate fields) which will manifest itself as a QlikView synthetic key. But have no fear. This synthetic key should be there; not only is it correct, but it is also optimal given the data model. You do not need to remove it.

IntervalMatch can also be used with an additional key between the tables – i.e. when you have Slowly Changing Dimensions. But more about that in a later post.

Source:IntervalMatch

Re: Interval between two timestamp

MarcoWedel — Fri, 07 Oct 2016 22:28:40 GMT

Hi,

one example using your sample TimeEntry.qvd having separate date and time fields:

LOAD EmployeeId,

ShiftId,

EntryDate,

InDate,

OutDate,

InTime,

InHour,

OutTime,

OutHour,

Interval(OutDate+OutHour-InDate-InHour) as InOutInterval

FROM [https://community.qlik.com/servlet/JiveServlet/download/1138458-248727/TimeEntry.Qvd] (qvd);

hope this helps

regards

Marco

Re: Interval between two timestamp

MarcoWedel — Fri, 07 Oct 2016 22:43:52 GMT

Your required interval between FromTime and InHour is a bit tricky because of a possible issue of your FromTime field.

It is not a time value, but has an additional integer part as well.

To use only the time part of this field, one solution might be:

Interval(InHour-Frac(FromTime))

(You might want to look into the qvd generator instead to correct the values and formatting there.)

hope this helps

regards

Marco

Re: Interval between two timestamp

hemachandran — Mon, 10 Oct 2016 05:23:59 GMT

Hi Macro,

Thank you for your response ..i did not able find the condition of whether lesser or greater between two fields..

Fromtime and inhour..Its wrongly showing the condition...

my code is this:

=if(time(InHour,'hh:mm:ss')>time(FromTime,'hh:mm:ss'),1,0)

Thanks in Advance..

Re: Interval between two timestamp

Anil_Babu_Samineni — Mon, 10 Oct 2016 09:36:43 GMT

Instead of that, Try this

=if(TimeStamp(TimeStamp#(InHour,'YourFormatHere''),hh:mm:ss')>TimeStamp(TimeStamp#(FromTime,''YourFormatHere'''),'hh:mm:ss'),1,0)

- ANIL

Re: Interval between two timestamp

MarcoWedel — Mon, 10 Oct 2016 20:43:42 GMT

Is that a different question?

Is your initial question answered?

regards

Marco