Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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;
;
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