topic Re: wrong format in QlikView

wrong format

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am using below

Timestamp#(Date("RDate")&' '&Time#(Right("RTime",8),'hh:mm:ss'),'MM/DD/YYYY hh:mm:ss') as "R Date Timestamp"

am getting output as 10/09/2017 78865741

but I need in in 'MM/DD/YYYY hh:mm:Ss' this format it is giving number I need hh:mm:ss

Rdate values: 10/09/2017

RTime values: 78865741 \

how to convert this?

Re: wrong format

marcus_malinow — Thu, 28 Sep 2017 08:49:15 GMT

Hi Sony,

your RTime field is being treated as a number of whole days - to convert to seconds you need to divide by 86400 (the number of seconds in a day)

For example:

Timestamp#(Date(RDate)&' '&Time(Right(RTime,8)/86400,'hh:mm:ss'),'MM/DD/YYYY hh:mm:ss') as [R Date Timestamp]

Marcus

Re: wrong format

Peter_Cammaert — Thu, 28 Sep 2017 09:01:13 GMT

How about a simpler expression.

TimeStamp(Date#(RDate) + Time#(Right(RTime, 8), 'hh:mm:ss'), 'MM/DD/YYYY') AS [R Date Timestamp]

But we can only test this in a reliable way if we get some real data for RDate and RTime. Are they QlikViewq dual fields? Or just strings from an external source?

Re: wrong format

PrashantSangle — Thu, 28 Sep 2017 09:05:11 GMT

Try below

Date(RDate)&' '&Time(RTime)

what is converted valued of 78865741 in time?

Regards,

Re: wrong format

soniasweety — Thu, 28 Sep 2017 09:25:26 GMT

not working

Re: wrong format

soniasweety — Thu, 28 Sep 2017 09:25:43 GMT

not working

Re: wrong format

PrashantSangle — Thu, 28 Sep 2017 09:33:44 GMT

can provide sample file.

What output you are getting after applying above expression?

Regards,

Re: wrong format

mohammadkhatimi — Thu, 28 Sep 2017 09:37:18 GMT

can u provide sample data so that it is eay to resolve ur problem..

Re: wrong format

prieper — Thu, 28 Sep 2017 09:52:32 GMT

Please use

TIMESTAMP#(RDate, 'MM/DD/YYYY hh:mm:ss')

in order to transform the field containing the data.

Further you may convert this into a format, in which it should be shown:

TIMESTAMP(TIMESTAMP#(RDate, 'MM/DD/YYYY hh:mm:ss'), 'YYYYMMDD hh:mm')

Re: wrong format

Kushal_Chawda — Thu, 28 Sep 2017 11:52:03 GMT

RTime values are in minutes or seconds?