Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
shaan007
Partner
Partner

Duplicate Date time values in X-axis

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


1 Solution

Accepted Solutions
mov
Employee
Employee

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

View solution in original post

20 Replies
rgvavihs
Specialist
Specialist

try with distinct keyword ! hope it will help you

shaan007
Partner
Partner
Author

Thank you for your reply. I did try that

Colin-Albert

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]

shaan007
Partner
Partner
Author

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.

Colin-Albert

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

shaan007
Partner
Partner
Author

Thank you Collin.

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

its so strange. Its giving Null value

Colin-Albert

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

Colin-Albert

This should work

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

jonathandienst
Partner
Partner

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein