topic Re: Formatting datefield which contains timestamp in QlikView

Formatting datefield which contains timestamp

Sat, 25 Jun 2011 08:31:13 GMT

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

Re: Formatting datefield which contains timestamp

Sat, 25 Jun 2011 09:18:15 GMT

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

Re: Formatting datefield which contains timestamp

Miguel_Angel_Baeyens — Sat, 25 Jun 2011 09:46:33 GMT

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

Re: Formatting datefield which contains timestamp

Sat, 25 Jun 2011 13:04:17 GMT

Hi Miguel

Thanks alot for your support!

Thanks

Attitude

Re: Formatting datefield which contains timestamp

Mon, 25 Jul 2011 14:11:59 GMT

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

Re: Formatting datefield which contains timestamp

Miguel_Angel_Baeyens — Mon, 25 Jul 2011 16:47:06 GMT

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

Re: Formatting datefield which contains timestamp

deepakk — Tue, 26 Jul 2011 02:56:04 GMT

HI Attitude,

Try out this method.

Re: Formatting datefield which contains timestamp

Tue, 26 Jul 2011 04:38:01 GMT

Hi Miguel

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

Regards

Attitude

Re: Formatting datefield which contains timestamp

Tue, 26 Jul 2011 04:39:21 GMT

Hi Deepak

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

Regards

Attitude

Re: Formatting datefield which contains timestamp

Tue, 26 Jul 2011 05:03:42 GMT

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

Re: Formatting datefield which contains timestamp

Miguel_Angel_Baeyens — Tue, 26 Jul 2011 07:07:00 GMT

Hi,

That sounds quite strange. If "FieldName" doesn't exist in your data model more than once, meaning is the only field in your model with that name, you can rename it as you like, and is very usual to rename a field with its own name after doing the required transformation.

Indeed, the following is working for me using your file as posted above, and it keeps only one date, getting ride of the decimal (time) part.

Table2:
LOAD Date(Floor(Login)) AS Login
FROM Login1.qvd (qvd);

EDIT: It's possible that you are loading later that Login field in some other part of your script without formatting and that messes up the data. Check your file attached.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica