Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sagar_Apte
Contributor III
Contributor III

Timestamp with iterno() bug

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?

 

 

 

 

4 Solutions

Accepted Solutions
marcus_sommer

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

View solution in original post

Vegar
MVP
MVP

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

View solution in original post

Kushal_Chawda

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

Screenshot 2020-09-25 140615.png

View solution in original post

marcus_sommer

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 

View solution in original post

14 Replies
Sagar_Apte
Contributor III
Contributor III
Author

marcus_sommer

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
MVP
MVP

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

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

Screenshot 2020-09-25 140615.png

Sagar_Apte
Contributor III
Contributor III
Author

@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

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
Contributor III
Contributor III
Author

Thank you all once again

jakobjosef
Creator II
Creator II

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