topic Re: RE:Date format Issue in QlikView

RE:Date format Issue

Chanty4u — Thu, 31 Mar 2016 04:58:40 GMT

Hi all,

input date format

column name: Voice_date

Data is in below format for Voice_date

August 31, 2015 9:00:00 PM IST

February 13, 2015 4:30:00 PM IST

September 6, 2015 5:00:00 AM IST

Required format output

YYYYMMDD

Thanks in advance

Best

Chanty

Re: RE:Date format Issue

settu_periasamy — Thu, 31 Mar 2016 05:09:20 GMT

Hi,

Try

T1:
LOAD *,Date(Floor(Date#(SubField(VoiceDate,' IST',1),'MMMM DD, YYYY hh:mm:ss TT')),'YYYYMMDD') as Date;

LOAD * INLINE [
  VoiceDate
    "August 31, 2015 9:00:00 PM IST"
    "February 13, 2015 4:30:00 PM IST"
    "September 6, 2015 5:00:00 AM IST"
];

Re: RE:Date format Issue

clondono — Thu, 31 Mar 2016 05:10:52 GMT

Hi Chanty,

In the object properties select the number tab and check "Override Document Settings". Select date and enter the Format Pattern you are looking for:

Re: RE:Date format Issue

tamilarasu — Thu, 31 Mar 2016 05:11:58 GMT

Also try,

Load *,

Date(Date#(Left(Voice_date, Index(Voice_date, ' ', 3)),'MMMM DD, YYYY'),'YYYYDDMM') as Date

From Source;

Re: RE:Date format Issue

Chanty4u — Thu, 31 Mar 2016 05:13:27 GMT

tq settu.

is it possible to create in Sql? using Coalesce Function

by that we can implement in Qvd's.

Re: RE:Date format Issue

Chanty4u — Thu, 31 Mar 2016 05:18:10 GMT

thnx tamil.

bt is this posible to get in SQl?

Re: RE:Date format Issue

settu_periasamy — Thu, 31 Mar 2016 05:32:35 GMT

No Idea how to relate this date format with Coalesce function in Sql. I assume you are talking about 'Replace Null Values'

May be you can look it here..

Replace Null Values

Coalesce statement in Qlikview‌

And from "QlikView for Developers Cookbook"

Re: RE:Date format Issue

Chanty4u — Thu, 31 Mar 2016 05:39:58 GMT

Not that settu.

chk dis sample example:

coalesce ( (TO_CHAR( Voice_DATE_NEW, 'DY')|| ' - ' || DATE(Voice_DATE_NEW)) ,(TO_CHAR( al_air_date, 'DY')|| ' - ' || date(al_air_date)), (TO_CHAR( L.DATE, 'DY')|| ' - ' || date(AL.DATE)) ) as Voice_DATE,

this expression gives us the output as "WED - 2016-02-17" but i need in YYYYMMDD

Re: RE:Date format Issue

HirisH_V7 — Thu, 31 Mar 2016 05:52:31 GMT

Hi,

Check this,

Date:
LOAD *,
TimeStamp(Timestamp#(Replace(Voice_date,Right(Voice_date,4),''),'MMMM DD, YYYY hh:mm:ss TT'),'DD/MM/YYYY h:mm:ss[.fff] TT') as TimeStamp,
Date(Timestamp#(Replace(Voice_date,Right(Voice_date,4),''),'MMMM DD, YYYY hh:mm:ss TT'),'YYYYMMDD')as DateField


 INLINE [
    Voice_date
    "August 31, 2015 9:00:00 PM IST"
    "February 13, 2015 4:30:00 PM IST"
    "September 6, 2015 5:00:00 AM IST"
];

Front end:

Hope this Helps,

PFA,

Hirish

Re: RE:Date format Issue

tamilarasu — Thu, 31 Mar 2016 05:53:15 GMT

Coalesce is similar to Alt function in Qlikview. Try below Sql statement.

coalesce ( DATE_FORMAT(Voice_DATE_NEW,'%Y%m%d') ,DATE_FORMAT(al_air_date,'%Y%m%d'), DATE_FORMAT(AL.DATE,'%Y%m%d') ) as Voice_DATE,

Re: RE:Date format Issue

Kushal_Chawda — Thu, 31 Mar 2016 05:56:20 GMT

try this

TO_DATE(TO_CHAR(SUBSTR(Date,1, length(Date)-4),'MONTH DD, YYYY HH:MI:SS AM'),'YYYYMMDD')

Re: RE:Date format Issue

Chanty4u — Thu, 31 Mar 2016 10:09:49 GMT

Thanks for response.

But its not in Qlikview it is Qliksense

Re: RE:Date format Issue

Chanty4u — Thu, 31 Mar 2016 10:10:08 GMT

Raj...

its givng error.

Re: RE:Date format Issue

tamilarasu — Fri, 01 Apr 2016 13:12:36 GMT

What is the error messge you are getting.?