topic Re: Duplicate time values in Listbox in QlikView

Duplicate Date time values in X-axis

shaan007 — Thu, 09 Apr 2015 07:08:22 GMT

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

Re: Duplicate time values in Listbox

Anonymous — Thu, 09 Apr 2015 07:24:44 GMT

try with distinct keyword ! hope it will help you

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 07:27:44 GMT

Thank you for your reply. I did try that

Re: Duplicate time values in Listbox

Colin-Albert — Thu, 09 Apr 2015 08:44:02 GMT

you need to round your timestamp values to the minute. The expression you have used is formatting the text representation of the timestamp but is not rounding the numeric portion so several different numeric values for timestamp share the same text representation hence you see duplicates.

Floor will round the values down to the nearest minute

Try using this to round down to the nearest minute

TimeStamp(floor(num([Trans Start DateTime])+(((IterNo()-1)/60/24)), 1/(24 *60)),'D/MM/YYYY h:mm:ss TT') as [TransCont DateTime]

This will round down to the nearest 15 minutes

TimeStamp(floor(num([Trans Start DateTime])+(((IterNo()-1)/60/24)), 15/(24 *60)),'D/MM/YYYY h:mm:ss TT') as [TransCont DateTime]

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 08:54:48 GMT

Thank you For the reply Collin.

Floor didnt help.

it still create more fluctuation in the chart.

Please refer the correct chart image in my attachment.

NOTE:

I tried this code listed below ..WORKED!!! still one problem,....

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

this giving me correct chart but I cannot view the normal date time . its all in Numeric value.

when I try this

TimeStamp(TimeStamp#(Num([Trans Start DateTime])+(((IterNo()-1)/60/24)),'D/MM/YYYY h:mm:ss TT'),'D/MM/YYYY h:mm:ss TT')

I see nothing in the chart.

i have no idea whats causing the problem.

Re: Duplicate time values in Listbox

Colin-Albert — Thu, 09 Apr 2015 08:59:30 GMT

Try this

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

Timestamp#() converts text to a timestamp value using the default or specified format for the input text

Timestamp() formats a numeric value using the default timestamp format or the specified format for the output data

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 09:06:44 GMT

Thank you Collin.

I have already tried that Code- Timestamp(TimeStamp#(Num....

its so strange. Its giving Null value

Re: Duplicate time values in Listbox

Colin-Albert — Thu, 09 Apr 2015 10:53:01 GMT

Can you post some example values for Num([Trans Start DateTime])

Re: Duplicate time values in Listbox

Colin-Albert — Thu, 09 Apr 2015 11:05:44 GMT

This should work

timestamp ( floor( (num( Start DateTime]) + ((IterNo()-1)/60/24) ), 1/(24 * 60) ))

Re: Duplicate time values in Listbox

jonathandienst — Thu, 09 Apr 2015 11:19:31 GMT

If I understand you correctly, you want times rounded to the nearest minute. This should do it:

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

HTH

Jonathan

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 21:01:21 GMT

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

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 21:01:58 GMT

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

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 21:35:40 GMT

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

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 21:46:33 GMT

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,

Re: Duplicate time values in Listbox

shaan007 — Thu, 09 Apr 2015 22:58:00 GMT

How many decimal places We need to consider for minutes?

40643.30902777777400 - 5 decimal?

Re: Duplicate time values in Listbox

rwunderlich — Fri, 10 Apr 2015 00:14:20 GMT

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

Re: Duplicate time values in Listbox

shaan007 — Mon, 13 Apr 2015 00:33:10 GMT

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.

Re: Duplicate time values in Listbox

Anonymous — Mon, 13 Apr 2015 01:29:38 GMT

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

Re: Duplicate time values in Listbox

rwunderlich — Mon, 13 Apr 2015 17:37:00 GMT

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

Re: Duplicate Date time values in X-axis

shaan007 — Mon, 13 Apr 2015 23:17:40 GMT

Thank you Rob,

The Chart still showing Duplicate Values in x axis.

Thank you