Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zul101xqx
Contributor II
Contributor II

convert to date

Just attached the Date file in Excel. I tried the below suggestions, but no luck.

Hi, I have below data in Excel, the default format is not date. I would like to  keep only the date and change the format to  MM/DD/YYYY after loading into Qlik. I tried date(floor(date#(Date,'M/D/YYYY h:mm')),'MM/DD/YYYY'), but no luck.

Any suggestions? Thanks!

PS, in Excel it actually is 9/30/2015  12:00:00 AM, but displayed as 9/30/2015 0:00

Date

6/30/2015 0:00
7/31/2015 0:00
8/31/2015 0:00
9/30/2015 0:00
10/31/2015 0:00
11/30/2015 0:00
12/31/2015 0:00
1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Hi Ze Li,

Maybe try,

Date(SubField(Data,' ',1),'MM/DD/YYYY')

View solution in original post

10 Replies
Anonymous
Not applicable

try

date(floor(date#(YOURDATEFIELD,'M/D/YYYY h:mm')),'MM/DD/YYYY')

zul101xqx
Contributor II
Contributor II
Author

I tried using Date# and Floor, no luck

Anonymous
Not applicable

for me it works, I forced text()  because I don't know whats your default date format...

If your default dateformat fits, then you don't have to convert anything at all...

LOAD date(floor(date#(Datefield,'M/D/YYYY h:mm')),'MM/DD/YYYY') as Datefield;
LOAD text(Datefield) as Datefield INLINE [
Datefield
6/30/2015 0:00
7/31/2015 0:00
8/31/2015 0:00
9/30/2015 0:00
10/31/2015 0:00
11/30/2015 0:00
12/31/2015 0:00
]
;

Anil_Babu_Samineni

Can you provide Excel file values. And look below may be help

QlikView Date fields

Get the Dates Right

Why don’t my dates work?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
gameoverzx
Partner - Contributor III
Partner - Contributor III

Hi Ze Li,

I have created a datasheet with you dates on excel and uploaded them to Qlik Sense and true enough, the fields are saved as your order.

Do follow the steps below :

1. Click 'edit this table' in the Data Manager

1.png

2. Click the date symbol

2.png

3. Click the display format to MM/DD/YYYY

3.png

Cheers, let me know if this works.

Regards,

Gaston

gameoverzx
Partner - Contributor III
Partner - Contributor III

I realised that you are using Qlik View instead, my apologies.

Still, if there is any functionality of Qlik View which allows you to prepare the data before loading it in, it should be capable of changing the display format. Otherwise, date(date#(Date,'M/D/YYYY h:mm'),'MM/DD/YYYY') should work.

You can try to troubleshoot this first by keying in date#(Date,'M/D/YYYY h:mm') and seeing it QlikView recognises if as the correct date format (output should be a number), play around with values such as M/DD/YYYY h:mm or M/D/YYYY hh:mm, once you get the first part right, you should be able to display in as your desired format thereafter.


Regards,

Gaston

anilnaidu13
Creator
Creator

Try This one


date(timestamp#([start_date],'M/D/YYYY h:mm'), 'MM/DD/YYYY')

brijeshvma
Partner - Creator
Partner - Creator

Provide Some Data in Excel

brijeshvma
Partner - Creator
Partner - Creator

Hi,

If data in AM and PM as well..So try with This

LOAD date(floor(date#(Datefield,'M/D/YYYY h:mm TT')),'MM/DD/YYYY') as Datefield;


As well its not working so please provide Excel for the same