topic Re: Timestamp with iterno() bug in App Development

Timestamp with iterno() bug

Sagar_Apte — Fri, 25 Sep 2020 09:35:08 GMT

Hi All,

I am trying generate timestamp for every 1 minute of interval between start date and end. I found the way of doing it but facing issue as in some scenario it is behaving strange. I am not sure if I am doing wrong , need your help.

I am running below script

Data: LOAD *, timestamp(Start_Date+MakeTime(0,IterNo()-1)) as Time While Start_Date+MakeTime(0,IterNo()-1)<=End_Date; LOAD * Inline [ Start_Date,End_Date 10/20/2020 10:00,10/20/2020 10:07 ];

For a interval defined above, I am getting correct output which is generating time from 10:00 to 10:07. But as soon as I put below interval with same script, it is not giving correct results.

Data:
LOAD *,
timestamp(Start_Date+MakeTime(0,IterNo()-1)) as Time
While Start_Date+MakeTime(0,IterNo()-1)<=End_Date;
LOAD * Inline [
Start_Date,End_Date
10/20/2020 11:00,10/20/2020 11:07 ];

Above script generates time only till 11:06 with missing 11:07. Not sure if it is bug or I am doing something wrong. Can you please advise?

Re: Timestamp with iterno() bug

Sagar_Apte — Fri, 25 Sep 2020 11:56:02 GMT

@marcus_sommer @swuehl @tresB @sunny_talwar @Kushal_Chawda

Re: Timestamp with iterno() bug

marcus_sommer — Fri, 25 Sep 2020 12:05:01 GMT

It's not a matter from iterno() else from the in Qlik used binary number system. Therefore try it in this way:

Data:
LOAD *,
timestamp(floor(Start_Date+MakeTime(0,IterNo()-1), 1/24/60)) as Time
While floor(Start_Date+MakeTime(0,IterNo()-1), 1/24/60) <=floor(End_Date, 1/24/60);
LOAD * Inline [
Start_Date,End_Date
10/20/2020 11:00,10/20/2020 11:07 ];

- Marcus

Re: Timestamp with iterno() bug

Vegar — Fri, 25 Sep 2020 12:29:37 GMT

It is a tiny decimal difference between the numeric representation of 10/20/2020 11:07 and (Start_Date+MakeTime(0,8-1))

Numeric of 10/20/2020 11:07 is: 44 124,46319444444000

Numeric of (Start_Date+MakeTime(0,8-1)) is 44 124,46319444445000

I reccomend you to round your timestapt down to seconds. Like this:

Data:
LOAD
*,
IterNo() as Iterno,
timestamp(Start_Date+MakeTime(0,IterNo()-1)) as Time
While
round(Start_Date+MakeTime(0,IterNo()-1),1/1440)<=round(End_Date,1/1440);
LOAD * Inline [
Start_Date,End_Date
10/20/2020 11:00,10/20/2020 11:07
10/20/2020 10:00,10/20/2020 10:07 ];

Re: Timestamp with iterno() bug

Kushal_Chawda — Fri, 25 Sep 2020 13:12:15 GMT

@Sagar_Apte as mentioned by @Vegar @marcus_sommer it could be due to decimal difference of your time part, you could probably also allow interno() to loop for actual minute difference of your start and end date like below to avoid any conversion issues

Data: LOAD *, timestamp(Start_Date+MakeTime(0,IterNo()-1)) as Time While IterNo()<= Minute(End_Date-Start_Date)+1; LOAD * Inline [ Start_Date,End_Date 10/20/2020 11:00,10/20/2020 11:07 10/20/2020 10:00,10/20/2020 10:07 ];

Re: Timestamp with iterno() bug

Sagar_Apte — Fri, 25 Sep 2020 13:31:22 GMT

@marcus_sommer @Vegar @Kushal_Chawda Thank you so much for the explanation. It is really helpful. Now I am confused which one I accept as solution as all are correct. Marcus was first so should I mark that as correct one?

Re: Timestamp with iterno() bug

marcus_sommer — Fri, 25 Sep 2020 13:35:57 GMT

Personally I prefer an equally rounding each time a time/timestamp is loaded or created/calculated to ensure that they always match in the expected way and there might be various other occasions in the script or the UI where such a match is performed.

In this case it's quite obvious why it didn't work but to find this issue in a join which only lost a few records (which may only happens later with real-data and not during the development) or within complex set analysis it might take much longer to realize the true cause (and I did already run into it a few times ...).

- Marcus

Re: Timestamp with iterno() bug

marcus_sommer — Fri, 25 Sep 2020 13:37:50 GMT

AFAIK you could mark several answers as correct.

- Marcus

Re: Timestamp with iterno() bug

Sagar_Apte — Fri, 25 Sep 2020 13:52:55 GMT

Thank you all once again

Re: Timestamp with iterno() bug

jakobjosef — Thu, 17 Jun 2021 14:35:33 GMT

Hi @marcus_sommer and @Vegar ,

i had the same issue, and your solution works fine, but (in my case ?) only until 60 iterations, then it stops. is there a solution for this problem as well, for example it the duration is about 2.5 hours?
I tried with timestamp but did non manage to find a proper solution 😞

i would be very glad if you could help me.

greetings jakob

Re: Timestamp with iterno() bug

jakobjosef — Thu, 17 Jun 2021 14:37:51 GMT

Re: Timestamp with iterno() bug

marcus_sommer — Thu, 17 Jun 2021 15:10:33 GMT

Iterno() hasn't any inbuilt limitation else it will run as far as the while-condition is true. This means you should check this condition again if it's really based on a minute-difference between from and to.

Before - just check the set selections in your application if not any of them limit your view from the screenshot to the 8'th hour.

- Marcus

Re: Timestamp with iterno() bug

jakobjosef — Fri, 18 Jun 2021 06:43:54 GMT

Dear @marcus_sommer ,
thank you very much for your answer. Unfortunately I could not find the problem.
But I made a very short example, using exactly your solution, maybe you could check it ?
This is the script :

The file is attached. I would be very happy if there is a solution to this problem.

Greetings
Jakob

Re: Timestamp with iterno() bug

jakobjosef — Fri, 18 Jun 2021 07:57:36 GMT

Could it be, that "maketime" can only add 59 minutes at the "minutes-spot"? so we would need to go on with the "hour-spot"?

Re: Timestamp with iterno() bug

marcus_sommer — Mon, 21 Jun 2021 06:10:55 GMT

Yes, you couldn't use maketime() in this scenario because you didn't trigger the next hour if you reach 60 minutes - probably there would be also measures to implement such logic with maketime() but I think it would be far much complicated as necessary and therefore I suggest something like:

timestamp(floor(FromDate + (1/24/60 * (iterno()-1)), 1/24/60))

- Marcus