topic Re: Filling in Timestamp values between existing timestamps in App Development

Filling in Timestamp values between existing timestamps

MATC — Fri, 15 Dec 2023 12:14:35 GMT

Hello,

I have this issue where I need to fill in Timestamp values inbetween existing ones (table below)

(For example I need to have an ID (it can be autogenerated values) from 2023-06-28 06:00:00 till 2023-06-28 06:26:25
then next one from 2023-06-28 06:26:27 till 2023-06-28 06:29:34 etc.

Can this be achieved in Qlik Sense?

Re: Filling in Timestamp values between existing timestamps

hic — Fri, 15 Dec 2023 14:05:08 GMT

Yes, you can. Take a look at https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944 and you will see the principles.

Do you want one datapoint per minute? Then try

TimeStamp( Floor(Start,1/24/60) + (IterNo() – 1)/24/60 ) as ReferenceTime

Or second? Then try

TimeStamp( Floor(Start,1/24/60/60) + (IterNo() – 1)/24/60/60 ) as ReferenceTime

Re: Filling in Timestamp values between existing timestamps

vincent_ardiet_ — Fri, 15 Dec 2023 14:35:36 GMT

You can also use the previous() function to do this.
For example, by adding this code after you have loaded the data:
Concatenate(myTable)
Load -1*RowNo() as ID, Previous(End)+(1/86400) as End,Next(Start)-(1/86400) as End Resident myTable Where Not IsNull(Previous(End))
Order By ID,Start;
;

Re: Filling in Timestamp values between existing timestamps

MATC — Wed, 03 Jan 2024 07:21:11 GMT

Hello,

Thank you for the answer it helped me to understand that the my issue goes deeper. This works partially in my case but unfortunately can't get it right because I need it to work dynamically on the front within object.

User can filter multiple ID's (which are aggregated by other fields) and every aggregation needs to be calculated separately.
Now it is being calculated based on the Value and i would need it to be calculated on the fly for the aggregated field (which can contain a lot of individual values)

Below example with the aggregated field containing 2 Values (autogenerated by singular Value in similar fashion as proposed above)

Not sure if it is understandable as it is rather complex case. Basically i need to find gaps between selected On and Off fields but dynamically and generate the time values for these gaps