Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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>
hh:mm should be in small case.. Not HH:MM
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
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/503524?et=watches.email.thread#503524>
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
Like
Load FieldName1,
FieldName1,
Date(FLOOR(dateFieldName),'YYYYMMDD') as NewDate
FROM
[Table1.xlsx]
(ooxml, embedded labels, table is input) group by Date(FLOOR(dateFieldName),'YYYYMMDD')
OR
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,
PS
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>
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