topic Re: convert to date in QlikView

convert to date

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

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

Re: convert to date

Anonymous — Wed, 10 May 2017 20:06:37 GMT

try

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

Re: convert to date

Anonymous — Wed, 10 May 2017 20:10:17 GMT

I tried using Date# and Floor, no luck

Re: convert to date

Anonymous — Wed, 10 May 2017 20:15:11 GMT

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
];

Re: convert to date

Anil_Babu_Samineni — Thu, 11 May 2017 05:26:19 GMT

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?

Re: convert to date

Anonymous — Thu, 11 May 2017 05:55:52 GMT

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

2. Click the date symbol

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

Cheers, let me know if this works.

Regards,

Gaston

Re: convert to date

Anonymous — Thu, 11 May 2017 06:02:17 GMT

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

Re: convert to date

Anonymous — Thu, 11 May 2017 06:03:49 GMT

Try This one

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

Re: convert to date

brijeshvma — Thu, 11 May 2017 06:59:20 GMT

Provide Some Data in Excel

Re: convert to date

brijeshvma — Thu, 11 May 2017 07:01:14 GMT

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

Re: convert to date

tamilarasu — Thu, 11 May 2017 08:08:08 GMT

Hi Ze Li,

Maybe try,

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