topic Re: How to convert a timestamp column in Year YYYY format? in QlikView

How to convert a timestamp column in Year YYYY format?

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

Hi,

I tried using the YEAR() function but I am not getting any records.

I imported an excel file in my qlikview and I wanted to extract year from a timestamp column, but I am unbale to do so.

What could be the error?

PFA screenshot

I want to Year from the ORDERED DATE list box,

for example

2014

2015

2016

Thanks

Re: How to convert a timestamp column in Year YYYY format?

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:15:31 GMT

This either in Script / Listbox Expression ..

=Date(Date#([ORDERED DATE],'DD/MM/YY hh:mm TT'),'YYYY')

Re: How to convert a timestamp column in Year YYYY format?

tresB — Thu, 12 Oct 2017 06:16:19 GMT

Try like:

Year(Date#([Ordered Date], 'DD/MM/YY hh:mm TT'))

Re: How to convert a timestamp column in Year YYYY format?

its_anandrjs — Thu, 12 Oct 2017 06:29:41 GMT

This one

Year( Date(Date#([Ordered Date], 'DD/MM/YY hh:mm TT'),'YYYY') )

Re: How to convert a timestamp column in Year YYYY format?

Anonymous — Thu, 12 Oct 2017 06:34:15 GMT

Perfect, Thanks

But now I want distinct year in the list box, how do I get that?

Re: How to convert a timestamp column in Year YYYY format?

Anonymous — Thu, 12 Oct 2017 06:36:27 GMT

Thanks, even this worked. But I want distinct values in listbox.

Re: How to convert a timestamp column in Year YYYY format?

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:40:18 GMT

For Distinct May be this?

In script

Load Distinct Date(Date#([ORDERED DATE],'DD/MM/YY hh:mm TT'),'YYYY') as DateFieldName;

In Listbox, Try this? and give it caption as business

Aggr(Date(Date#([ORDERED DATE],'DD/MM/YY hh:mm TT'),'YYYY'), [ORDERED DATE])

Re: How to convert a timestamp column in Year YYYY format?

tresB — Thu, 12 Oct 2017 06:40:56 GMT

Using year() as I showed above should give distinct values in list box. Could share a sample qvw.

Re: How to convert a timestamp column in Year YYYY format?

Anonymous — Thu, 12 Oct 2017 06:41:53 GMT

Hey, it worked when applied in script...got distinct year! Thanks a lot.

Re: How to convert a timestamp column in Year YYYY format?

its_anandrjs — Thu, 12 Oct 2017 12:42:53 GMT

Can you close the thread now with appropriate answers correct / Helpful or references.

Re: How to convert a timestamp column in Year YYYY format?

its_anandrjs — Thu, 12 Oct 2017 13:55:47 GMT

You can try this also

Year( DISTINCT Date(Date#([Ordered Date], 'DD/MM/YY hh:mm TT'),'YYYY') )