Timestamp with iterno() bug

[Sagar_Apte]

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?

[Sagar_Apte]

@marcus_sommer @swuehl @tresesco @sunny_talwar @Kushal_Chawda 

[marcus_sommer · Accepted solution]

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

[Vegar · Accepted solution]

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 ];

[Kushal_Chawda · Accepted solution]

@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 ];

[Sagar_Apte]

@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?

[marcus_sommer · Accepted solution]

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 

[marcus_sommer]

AFAIK you could mark several answers as correct.

- Marcus

[Sagar_Apte]

Thank you all once again

[jakobjosef]

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