Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Formatting datefield which contains timestamp

Hi All

I am trying to load only the date from a field which contains date with time stamp. I tried the below code but it is not working as expected. Could you please help me out on this? I have attached the sample application with the qvd file where I am loading from.

Login1:

LOAD Date(Login,'DD/MM/YYYY') as Login

FROM

C:\Login1.qvd(qvd);

Thanks

Attitude

10 Replies
Highlighted
Not applicable

Hi All

Below code has done the trick. Is there is anyother alternative for this? If yes please do let me know.

date(floor(num(field)))


Thanks

Attitude

Highlighted

Hello,

You can leave the date and floor functions, and that's the correct way to get the results you want.

Date(Floor(Login))

Floor() rounds the number found in Login down. That's needed because you have some timestamps with decimals, meaning not just a date, but a date with hours and minutes -a timestamp- (DD/MM/YYYY hh:mm). When QlikView interprets a numeric value with decimals as a date, you can make it represent only the DD/MM/YYYY but the subjacent value still has the hour:minute information. That's where the floor() function makes sense.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Highlighted
Not applicable

Hi Miguel

Thanks alot for your support!

Thanks

Attitude

Highlighted
Not applicable

Hi Miguel

I used the same thing in the application but it is still giving the date with the timestamp. Please let me know what could be the reason behind that. Your solution did worked previously in different application. Here what could be the reason for that?

For your information when I use Date(Floor(FieldName)) in the script it is not working but whereas when I used the same in the front end it works. May I know how can I do that in the backend please.

Regards

Attitude

Highlighted

Hi,

Make sure first that the field has a numeric format, and how it is being read (thousand or decimal separator may avoid Floor() to work).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Highlighted
Partner
Partner

HI Attitude,

Try out this method.

Highlighted
Not applicable

Hi Miguel

It is in numeric format only. It is showing like this 31/12/2006 00:00:00

Regards

Attitude

Highlighted
Not applicable

Hi Deepak

Excellent code! Thanks... Hope that will solve my problem.

Regards

Attitude

Highlighted
Not applicable

Hi Miguel

Previous solution which you gave was working fine only. I did the mistake by giving the alias name which is as same as the field name.

Date(Floor(FieldName)) AS FieldName

When I did the change as below it worked 🙂

Date(Floor(FieldName)) AS FieldName1

Regards

Attitude