topic Re: Dates in QlikView

Dates

Thu, 03 Apr 2014 15:35:37 GMT

Good afternoon,

I don't know how I can convert a Date from Excel sheet in numeric format.

I received from Excell: '01/03/2014' and I want stored in a QVD as 20140301.

Thanks a lot for your help.

Re: Dates

PrashantSangle — Thu, 03 Apr 2014 15:38:14 GMT

Hi,

Use Date(Date#(datefield,'DD/MM/YYYY'),'YYYYMMDD') as NewDate,

Regards,

Re: Dates

its_anandrjs — Thu, 03 Apr 2014 15:44:29 GMT

Change like

Num(Date#('01/03/2014','DD/MM/YYYY')) as NewDatefield,

Date(Date#(DateField,'DD/MM/YYYY'),'YYYYMMDD') as NewDate

Re: Dates

Thu, 03 Apr 2014 15:45:21 GMT

I am sorry, I receive from Excell 01/03/2014 20:27 and the result shoud be 20140301

Re: Dates

PrashantSangle — Thu, 03 Apr 2014 15:46:27 GMT

Hi,

Try this,

Date(Date#(datefield,'DD/MM/YYYY hh:mm'),'YYYYMMDD') as NewDate,

Regards,

Re: Dates

yduval75 — Thu, 03 Apr 2014 15:47:20 GMT

Date(Datefield,'YYYYMMDD'')

Re: Dates

its_anandrjs — Thu, 03 Apr 2014 15:47:32 GMT

New Update please check now Actually your date is time stamp date..

Change like below

Date(Timestamp#(DateField,'DD/MM/YYYY hh:mm'),'YYYYMMDD') as NewDate

and

Num(Date(Timestamp#(DateField,'DD/MM/YYYY hh:mm'),'YYYYMMDD')) as NewDate

and

Date(Timestamp#('01/03/2014 20:27','DD/MM/YYYY hh:mm'),'YYYYMMDD')

Re: Dates

MK_QSL — Thu, 03 Apr 2014 15:47:51 GMT

Try below in script.

NUM(FLOOR(Date#('01/03/2014 20:27', 'DD/MM/YYYY hh:mm'))) as NEWDATE

NUM(FLOOR(Date#(YourDateField, 'DD/MM/YYYY hh:mm'))) as YourDateField

Date(FLOOR(Date#(YourDateField, 'DD/MM/YYYY hh:mm')),'YYYYMMDD') as YourDateField

Re: Dates

Thu, 03 Apr 2014 16:02:12 GMT

I put:

Date(Date#([Fecha del Ticket], 'DD/MM/YY HH:MM'), 'YYYYMMDD') as NewDate

And in the QVD the field is in blank

Re: Dates

Thu, 03 Apr 2014 16:04:56 GMT

Thanks a lot Joan and Anad

Re: Dates

Mon, 07 Apr 2014 11:41:53 GMT

If I use:

Date(FLOOR(Date#(, 'DD/MM/YYYY HH:MM')),'YYYYMMDD') as DRDATE, the QVD field is in blank and if I use:

Date(Date#(DateField,'DD/MM/YYYY HH:MM'),'YYYYMMDD') as NewDate

The file is populated ok, but after I can’t group by NewDate

De: Manish Kachhia

Enviado el: jueves, 03 de abril de 2014 17:49

Para: Salmerón, Joaquín

Asunto: Re: - Dates

Qlik Community<http://community.qlik.com/>

Dates

reply from Manish Kachhia<http://community.qlik.com/people/MRKachhiaIMP?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/501844?et=watches.email.thread#501844>

Re: Dates

MK_QSL — Mon, 07 Apr 2014 11:48:25 GMT

hh:mm should be in small case.. Not HH:MM

Re: Dates

Mon, 07 Apr 2014 12:01:41 GMT

Yes, I already checked, and is same problem.

De: Manish Kachhia

Enviado el: lunes, 07 de abril de 2014 13:49

Para: Salmerón, Joaquín

Asunto: Re: - Dates

Qlik Community<http://community.qlik.com/>

Dates

Re: Dates

PrashantSangle — Mon, 07 Apr 2014 16:34:28 GMT

Hi,

use

Date(FLOOR(dateFieldName),'YYYYMMDD') as NewDate

and

You can not use newly created field in group by clause,

if you want to use newly created field in group by clause the write the whole expression in group by clause

Load FieldName1,

FieldName1,

Date(FLOOR(dateFieldName),'YYYYMMDD') as NewDate

FROM

[Table1.xlsx]

(ooxml, embedded labels, table is input) group by Date(FLOOR(dateFieldName),'YYYYMMDD')

you can use it using resident load

qvd1:

Load FieldName1,

FieldName1,

Date(FLOOR(dateFieldName),'YYYYMMDD') as NewDate

FROM

[Table1.xlsx]

(ooxml, embedded labels, table is input);

Load *

Resident ABC group by NewDate;

Drop tables qvd1;

Regards,

Re: Dates

Mon, 07 Apr 2014 16:48:30 GMT

Thanks for you detailed explanation.

De: max dreamer

Enviado el: lunes, 07 de abril de 2014 18:35

Para: Salmerón, Joaquín

Asunto: Re: - Dates

Qlik Community<http://community.qlik.com/>

Dates

reply from max dreamer<http://community.qlik.com/people/dreamer4?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/503859?et=watches.email.thread#503859>

Re: Dates

its_anandrjs — Mon, 07 Apr 2014 16:55:52 GMT

If in same table you use Group by "Date(Date#(DateField,'DD/MM/YYYY HH:MM'),'YYYYMMDD') as NewDate" dont use field alias name "NewDate" use Date(Date#(DateField,'DD/MM/YYYY HH:MM'),'YYYYMMDD')

Load

Fields,

Date(Date#(DateField,'DD/MM/YYYY HH:MM'),'YYYYMMDD') as NewDate

.....

From Location

Group by Date(Date#(DateField,'DD/MM/YYYY HH:MM'),'YYYYMMDD');

Other wise use another resident load

Load

Fields,

NewDate

.....

From Location

Group by NewDate;

and drop previous table.

Regards