topic Re: Timestamp conversion to custom format in QlikView

Timestamp conversion to custom format

Mon, 08 Aug 2016 13:51:06 GMT

Hello everyone,

I have a column in one of my qvd with timestamp as in the below format.

30.05.2016 07:35 AM

03.06.2016 06:48 AM

19.02.2016 09:54 AM

05.05.2016 06:56 AM

08.05.2016 01:49 AM

14.12.2015 10:37 AM

14.12.2015 04:34 PM

28.12.2015 04:45 AM

I want to display this as mm/yyyy .

In one of my previous qvd the data was like below:

2013-10-13

2013-10-14

2013-10-15

I simply loaded and used the expression If(Year(Period)='2016',Aggr(DISTINCT(Year(Period)&'/'& Num(Month(Period))),Period)) to display in mm/yyyy format. But the same expression is not working in m current format 28.12.2015 04:45 AM .

I tried conversion functions, but no luck . please help.

-James

Re: Timestamp conversion to custom format

ecolomer — Mon, 08 Aug 2016 15:27:54 GMT

See this link:

Timestamp to Date format, but not working.

Re: Timestamp conversion to custom format

sunny_talwar — Mon, 08 Aug 2016 16:06:47 GMT

Try this in the script to normalize this into one date format:

LOAD Date(MonthStart(Alt(Num(Date#(DateField, 'DD.MM.YYYY hh:mm TT')), Num(Date#(DateField, 'YYYY-MM-DD')))), MM/YYYY) as MonthYear,

Date(Floor(Alt(Num(Date#(DateField, 'DD.MM.YYYY hh:mm TT')), Num(Date#(DateField, 'YYYY-MM-DD'))))) as DateField

Re: Timestamp conversion to custom format

maxgro — Mon, 08 Aug 2016 18:33:12 GMT

load

Date(

alt(floor(Date#(field, 'DD.MM.YYYY hh:mm TT')), Date#(field, 'YYYY-MM-DD')),

'MM/YYYY'

) as DateMMYYYY

inline [

field

30.05.2016 07:35 AM

03.06.2016 06:48 AM

19.02.2016 09:54 AM

05.05.2016 06:56 AM

08.05.2016 01:49 AM

14.12.2015 10:37 AM

14.12.2015 04:34 PM

28.12.2015 04:45 AM

2013-10-13

2013-10-14

2013-10-15

];

Re: Timestamp conversion to custom format

sunny_talwar — Mon, 08 Aug 2016 18:35:51 GMT

Massimo -

Wouldn't it make sense to add MonthStart() so that DateMMYYYY doesn't repeat in the listbox?

Re: Timestamp conversion to custom format

Tue, 09 Aug 2016 07:03:02 GMT

Sunny ,

Your load command:

Date(Floor(Alt(Num(Date#(Created, 'DD.MM.YYYY hh:mm TT')), Num(Date#(Created, 'YYYY-MM-DD'))))) worked when i used it along with my expression in my table:

=If(Year(NCFPeriod)='2016',Aggr(DISTINCT(Year(NCFPeriod)&'/'& Num(Month(NCFPeriod))),NCFPeriod))

But the sort order should be ascending according to date. Please find the snapshot where the sort is not correct even after giving 'ascending' in the 'sort' tab. please help.

- James

Re: Timestamp conversion to custom format

sunny_talwar — Tue, 09 Aug 2016 08:30:14 GMT

Can you share the script you are using for the Month field? It is formatted as YYYY/MM, not sure if it is text of date field

Re: Timestamp conversion to custom format

Tue, 09 Aug 2016 08:58:24 GMT

Sunny ,

It is working fine with the sorting Numeric value = Ascending and Text A->Z .

Thanks a ton !

-James

Re: Timestamp conversion to custom format

gabriel_as — Mon, 24 Oct 2016 23:37:18 GMT

La respuesta mas simple es:

al momento de hacer la consulta, ej:

LOAD

DocDate

;

SELECT

convert(varchar(10),cast(DateField as date),103) as DocDate

FROM MyTable;

103 es un codigo propio de los formatos de fecha en SQL, se pueden referir a la pagina:

https://msdn.microsoft.com/en-us/library/ms187928.aspx

y mas sencillo es como lo refiere el link Enrique Colomer

date(floor(DateField),'DD/MM/YYYY') as newDate

Saludos