Duplicate Date time values in X-axis

[shaan007]

I am having another strange problem here.

This is a List box of Transaction Date and time.

It’s not supposed to show duplicate values.

That’s mean those two dates are not the same.

Do u have any Idea?

please see my attachment image.I made that Date and time field from

TransactionALL:

LOAD
PK_TRANSACTION,
[Trans Start DateTime],
[Trans End DateTime]
FROM
TransactionALL.qvd
(qvd);

TransactionFlow:

load DISTINCT
PK_TRANSACTION
,TimeStamp(Num([Trans Start DateTime])+(((IterNo()-1)/60/24)),'D/MM/YYYY h:mm:ss TT') as [TransCont DateTime]
,1 as [TransCont Count]
Resident Transaction
While (Num([Trans Start DateTime])+(((IterNo()-1)/60/24))) < (Num([Trans End DateTime]))
;

[shaan007]

Hi Jonathan,

Thank you for the reply, I am getting Null when I use Round.

Plese have a look at the documents I have attached in this discussion.

Thank you

[shaan007]

Hi Collin,

Thank you for the reply, I am getting Null when I use Floor.

Plese have a look at the documents I have attached in this discussion.

Thank you

[shaan007]

if I apply this in script 

,Num(([Trans Start DateTime])+(((IterNo()-1)/60/24))) 

I'm getting values like -> 40634.309027778

In my Occupancy chart I get two 40634.309027778 in x-axis. there are more other duplicates too.

if I use a Textbox and test =Timestamp(40634.309027778)

i get the date Time -> 1/01/2011 7:25:00 AM

Please find My attachment with the Correct Chart.

if I apply this in script  

,Timestamp#(([Trans Start DateTime])+(((IterNo()-1)/60/24)),'DD/MM/YYYY h:mm:ss TT') as [TransCont DateTime]

Im getting the Correct occupancy Chart but the x-axis is showing date time Numeric Value.

All I need view the Date time in x axis. when i try timestamp(timeStamp#... I am getting Null Date time.

Thank you Collin

[shaan007]

UPDATE:

I noticed the Duplicate values 01/04/2014 7:25:00 AM

are 40643.30902777777400 and

      40643.30902777778100

this ([Trans Start DateTime])+(((IterNo()-1)/60/24)),'DD/MM/YYYY h:mm:ss TT')

causing the Problem,

[shaan007]

How many decimal places We need to consider for minutes?

40643.30902777777400 - 5 decimal?

[rwunderlich]

I always use time functions to avoid these rounding problems. See

http://qlikviewcookbook.com/2011/10/correct-time-arithmetic/

So the difference in minutes between two times is:

interval(End - Start, 'm')

A minute incrementing expression may be:

Start + interval#(iterno()-1.'m')

-Rob

[shaan007]

Hi Rob,

Thank you and Sorry for the late reply.

I cud not get the correct Chart.

Please chk my attachment.

The Chart showing correct line but the x axis has timestamp# values . but not showing the  Datetime. When i use the Ttimestamp(Timestamp#(... it creates all null value.

[Anonymous · Accepted solution]

See attached, the change is in line 26 in the script.

[rwunderlich]

I would create the minute intervals like this:

Temp_TransactionFlow:

NoConcatenate load

PK_TRANSACTION

,[Trans Start DateTime] + interval#(IterNo()-1,'m') as [TransCont DateTime]

,1 as [TransCont Count]

Resident Transaction

While [Trans Start DateTime] + interval#(IterNo()-1,'m') <= (Num([Trans End DateTime]));

See attached,

-Rob

[shaan007]

Thank you Rob,

The Chart still showing Duplicate Values in x axis.

Thank you