topic Re: Show date time in two separate columns in QlikView

Show date time in two separate columns

bhavvibudagam — Fri, 04 Mar 2016 13:56:38 GMT

Hi,

Can any one please help me.

I have one date filed in the format of '23/08/2016 06:55:60 PM' I want to display this Date in two separate columns i.e Date contains 23/08/2016 in one column and Time contains 06:55:60 PM in another column in script.

Re: Show date time in two separate columns

swuehl — Fri, 04 Mar 2016 18:57:01 GMT

There are three steps involved:

1) Interprete your timestamp, so QV read it in as dual value

2) Transform your value, e.g. split into date (integer part of the underlying numeric value) and time (fractional part)

3) Format values as needed

Data Types in QlikView

Get the Dates Right

Why don’t my dates work?

LOAD

TimeStampField,

Timestamp#(TimeStampField,'DD/MM/YYYY hh:mm:ss TT') as InterpretedTimestamp,

Date(Floor(Timestamp#(TimeStampField,'DD/MM/YYYY hh:mm:ss TT')),'DD/MM/YYYY') as Date,

Time(Frac(Timestamp#(TimeStampField,'DD/MM/YYYY hh:mm:ss TT')),'hh:mm:ss TT') as Time,

...

edit:

Instead of explicitely interpreting your values, you can also use and set the default format codes in the script accordingly.

Re: Show date time in two separate columns

maxgro — Fri, 04 Mar 2016 19:00:54 GMT

and these, copied from QlikView help

are the format code you can use to interpret and format

Dates

To describe the day, use the symbol "D" for each digit.
To describe the month number, use the symbol "M" or "MM" for one or two digits. "MMM" denotes short month name in letters as defined by the operating system or by the override system variable MonthNames in the script. "MMMM" denotes long month name in letters as defined by the operating system or by the override system variable LongMonthNames in the script.
To describe the year, use the symbol "Y" for each digit.
To describe the weekday, use the symbol "W". One W will return the number of the day (e.g. 0 for Monday) as a single digit. "WW" will return the number with two digits (e.g. 02 for Wednesday). "WWW" will show the short version of the weekday name (e.g. Mon) as defined by the operating system or by the override system variable DayName in the script. "WWWW" will show the long version of the weekday name (e.g. Monday) as defined by the operating system or by the override system variable LongDayName in the script
Arbitrary separators can be used.

Times

To describe the hours, use the symbol "h" for each digit.
To describe the minutes, use the symbol "m" for each digit.
To describe the seconds, use the symbol "s" for each digit.
To describe the fractions of a second, use the symbol "f" for each digit.
To describe the time in AM/PM format, use the symbol "tt" after the time.
Arbitrary separators can be used.

Time Stamps

The same notation as that of dates and times above is used.

Re: Show date time in two separate columns

MarcoWedel — Fri, 04 Mar 2016 23:31:59 GMT

there's also another function to extract the date part from a timestamp:

DayName(TimestampField) as DateField

regards

Marco

Re: Show date time in two separate columns

bhavvibudagam — Sat, 05 Mar 2016 09:58:46 GMT

Hi Swuehi,

Thank you so much.It's Working.

Re: Show date time in two separate columns

oknotsen — Sat, 05 Mar 2016 10:12:19 GMT

If your question is now answered, please flag the Correct Answer and possible Helpful Answers.

If not, please make clear what part of this question still needs answering .