topic Re: How to Show value in a Proper Timestamp Format in QlikView

How to Show value in a Proper Timestamp Format

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

Hi all , I am fetching a data from big Query . everything in fine . but Timestamp format i am getting in not Perfect .

Kindly help me to achieve this. please find the attachment.

i Attached , Qlikview output image , Bigquery image , sample data in excel and qvw .

Re: How to Show value in a Proper Timestamp Format

amit_saini — Fri, 15 Apr 2016 12:43:07 GMT

Hi,

Try setting from here:

Thanks

Re: How to Show value in a Proper Timestamp Format

sunny_talwar — Fri, 15 Apr 2016 12:45:14 GMT

In this file you are loading the data from the csv file, I think you need to fix this issue while laoding the data from Big Query. What is the script that you are using?

Re: How to Show value in a Proper Timestamp Format

Colin-Albert — Fri, 15 Apr 2016 12:45:33 GMT

These posts should help you get the correct timestamp values.

Why don’t my dates work?

QlikView Date fields

Re: How to Show value in a Proper Timestamp Format

sunny_talwar — Fri, 15 Apr 2016 12:45:59 GMT

Amit the date doesn't seem to be the correct date. Check the screenshot

Re: How to Show value in a Proper Timestamp Format

amit_saini — Fri, 15 Apr 2016 12:50:18 GMT

Yes Sir

Re: How to Show value in a Proper Timestamp Format

brijeshvma — Fri, 15 Apr 2016 12:57:25 GMT

Hi Sunny,

Thanks for your reply .

I am using a QvSource connector to fetch data .

i am attaching Qvd as well for the same .

Can you what i need to do .

Re: How to Show value in a Proper Timestamp Format

brijeshvma — Fri, 15 Apr 2016 13:23:39 GMT

Hi,

I have a script in Date_add(date_time,330,"MINUTE") as date_time_IST in big query to create this Field. can you able to tell me what i can do so qlikview can understand the timestamp format

Re: How to Show value in a Proper Timestamp Format

Peter_Cammaert — Fri, 15 Apr 2016 13:41:57 GMT

Can you tell us in what environment or which programming language these large timestamp values were created? QVSource extracts these values from somwhere. And digits are just digits. The cannot tell us anything about granularity or starting date.

Peter

Re: How to Show value in a Proper Timestamp Format

brijeshvma — Fri, 15 Apr 2016 13:46:08 GMT

i am extracting data from big query .

select date, Date_add(date_time,330,"MINUTE") as date_time_IST,datetime

from(

SELECT date,SEC_TO_TIMESTAMP(datetime/1000) as date_time ,datetime

FROM (

SELECT date,INTEGER(JSON_EXTRACT(hits.eventInfo.eventLabel,'$.time_stamp')) as datetime,

Re: How to Show value in a Proper Timestamp Format

settu_periasamy — Fri, 15 Apr 2016 13:57:33 GMT

May be try this..

SELECT TIMESTAMP(TimestampField);

in QV script,

ConverttoLocalTime(Date#('TimestampField','YYYY-MM-DD hh:mm:ss')) as Local_Timestamp

-This will convert your UTC time to Local system time format

Re: How to Show value in a Proper Timestamp Format

brijeshvma — Fri, 15 Apr 2016 14:04:26 GMT

Hi Settu,

I tried your given expression , nothing happend . can you check with my csv file or qvd that i attached.

Re: How to Show value in a Proper Timestamp Format

swuehl — Fri, 15 Apr 2016 14:08:29 GMT

I think your date time field is given in epoch time / unix time after your INTEGER conversion.

Epoch Converter - Unix Timestamp Converter

Either correct the SQL statement to bring in the textual representation, or transform your integer to Qlik date time:

Re: How to convert Unix Time Stamp

Re: How to Show value in a Proper Timestamp Format

settu_periasamy — Fri, 15 Apr 2016 14:26:39 GMT

Based on Swehul suggestion, Try this script.

LOAD   SynchronousQuery_date,
     TimeStamp(Makedate(1970,1,1)+ SynchronousQuery_date_time_IST /24/60/60) as SynchronousQuery_date_time_IST,
      TimeStamp(Makedate(1970,1,1)+ SynchronousQuery_datetime /24/60/60/1000) as SynchronousQuery_datetime,
      SynchronousQuery_device
FROM DateCheck.qvd (qvd);

Link : UNIX DATE to Qlikview DATE‌

Edit: Missed one Field, now its added, Sample Attached

Re: How to Show value in a Proper Timestamp Format

brijeshvma — Fri, 15 Apr 2016 14:47:31 GMT

Hi ,

Thanks swuehl and settu_periasamy,

Based on the swuehl suggestion . i got this community.qlik.com/message/700732#700732 . that works ..

same link as settu as well posted.

Re: How to Show value in a Proper Timestamp Format

brijeshvma — Fri, 15 Apr 2016 14:48:20 GMT

Thanks for your time settu , swuehl and everyone