topic Re: Date Format in QlikView

Date Format

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

Hello,

I have date field in excel. Now, i want to convert to qlikview date format. Suggest me, Because when i am trying to create one list box with the year it showing 1899 & 2014. But i have only 2014 Data in my excel. Please help me?

My excel date has 3/26/2014 12:00:00 PM

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

2014:

LOAD rep as [2014 rep],

franchise as [2014 franchise],

product as [2014 product],

Date as [2014 Date],

Year(Date) as [2014 Year],

Score as [2014 Score],

groupObservation as [2014 groupObservation],

name as [2014 name],

Username as [2014 Username]

FROM

[2014Data.xls]

Re: Date Format

shree909 — Wed, 06 Jul 2016 13:56:01 GMT

I think the date coming from the excel is string format, convert it to number

=Year(Floor(TIMESTAMP#('3/26/2014 12:00:00 PM','M/DD/YYYY hh:mm:ss TT'))) or

=Year(Floor(TIMESTAMP#(Date,'M/DD/YYYY hh:mm:ss TT')))

Replace the 3/26/2014 12:00:00 PM' with Date.

Re: Date Format

Anil_Babu_Samineni — Wed, 06 Jul 2016 14:29:04 GMT

Shree,

If we use this, After that i check the list box. List box showing empty.

Year(DATE(FLOOR(Timestamp(Date)),'MM/DD/YYYY HH:MM:SS TT'))

I am using this, I just want to ask you is that is correct one. Because, i got the solution after using this...

Re: Date Format

shree909 — Wed, 06 Jul 2016 14:43:48 GMT

ur missing the # sign after Timestamp and use the lower case for the time format as hh:mm:ss

Year(DATE(FLOOR(Timestamp#(Date)),'MM/DD/YYYY hh:mm:ss TT'))

Re: Date Format

Anil_Babu_Samineni — Wed, 06 Jul 2016 14:45:48 GMT

If i take the #. In list box it showing null.

Without # It's working fine.

Re: Date Format

shree909 — Wed, 06 Jul 2016 14:47:15 GMT

can you please share sample data..

if possible add this year to the script;

Re: Date Format

vinieme12 — Wed, 06 Jul 2016 14:52:35 GMT

OR you can use

=Year(SUBFIELD(Date,' ',1))

Re: Date Format

sunny_talwar — Thu, 07 Jul 2016 03:10:17 GMT

If this is working:

Year(DATE(FLOOR(Timestamp(Date)),'MM/DD/YYYY HH:MM:SS TT'))

Then try this:

Year(Date)

All those extra functions should not be required to calculated a Year field.

Re: Date Format

Anil_Babu_Samineni — Thu, 07 Jul 2016 07:42:14 GMT

Yes sunny, that is working.

You mentioned two expressions

Year(DATE(FLOOR(Timestamp(Date)),'MM/DD/YYYY HH:MM:SS TT'))

Year(Date)

Note: As i understand, after see this those two are same right?

When we use the Timestamp and Timestamp# . What are the major diff. Give me simple example for me if you are free...

Re: Date Format

Chanty4u — Thu, 07 Jul 2016 07:52:02 GMT

Hi anil,

timestamp(expression [ , format-code ])

The timestamp function formats the expression as a date and time according to the string given as format-code. If the format code is omitted, the date and time formats set in the operating system are used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY
Time format	hh:mm:ss	hh:mm:ss

timestamp( A ) where A=35648.375 returns:

	Setting 1	Setting 2
String	97-08-06 09:00:00	8/6/97 09:00:00
Number	35648.375	35648.375

timestamp( A,'YYYY-MM-DD hh.mm') where A=35648 returns:

Setting 1

Setting 2

String

1997-08-06 00.00

Number

35648

timestamp#(expression [ , format-code ])

The timestamp# function evaluates the expression as a date and time according to the string given as format-code. If the format code is omitted, the default date and time formats set in the operating system are used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY
Time format	hh:mm:ss	hh:mm:ss

timestamp#( A ) where A=8/6/97 09:00:00 returns:

	Setting 1	Setting 2
String	8/6/97 09:00:00	8/6/97 09:00:00
Number	-	35648.375

timestamp#( A, 'YYYY-MM-DD hh_mm' ) where A=8/6/97 09_00 returns:

	Setting 1	Setting 2
String	1997-08-06 09_00	1997-08-06 09_00
Number	35648.375	35648.375

Re: Date Format

MindaugasBacius — Thu, 07 Jul 2016 07:59:37 GMT

All the functions that ends #: time#, date#, money# etc. are interpretation functions. It means that the user defines how to interpret the data.

Re: Date Format

Anil_Babu_Samineni — Thu, 07 Jul 2016 08:32:27 GMT

Nice Chanty

Simple explanation and perfect understanding. Thanks guys.

Re: Date Format

sunny_talwar — Thu, 07 Jul 2016 09:43:34 GMT

Anil -

I think that you have not familiarized yourself with the QlikView/Qlik Sense Help yet. What Chanty provided are direct extract from the help sections. I would highly recommend using help to understand things which you don't understand before looking for help here because most of time you will find a good explanation and some examples to go with them on the help page

QlikView Help -> Home ‒ QlikView

Qlik Sense Help -> Home ‒ Qlik Sense

So on the right you see a list of functions you can get help on and I also highlighted Examples and results section which is initially closed, but when you click on it, it will show you some examples here.