Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Chanty4u
MVP
MVP

RE:Date format Issue

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

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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"

];

Capture.JPG

View solution in original post

13 Replies
settu_periasamy
Master III
Master III

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"

];

Capture.JPG

View solution in original post

clondono
Creator III
Creator III

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:

1.png

2.png

tamilarasu
Champion
Champion

Also try,

Load *,

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

From Source;

Chanty4u
MVP
MVP
Author

tq settu.

is it possible to create in Sql?   using   Coalesce Function

by that we can implement in Qvd's.

Chanty4u
MVP
MVP
Author

thnx tamil.

bt  is this posible to get in SQl?

settu_periasamy
Master III
Master III

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"

Capture.JPG

Chanty4u
MVP
MVP
Author

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  

HirisH_V7
Master
Master

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:

Date Format IST GMT-211532.PNG

Hope this Helps,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
tamilarasu
Champion
Champion

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,