topic Import dates from Excel in QlikView

Import dates from Excel

Tue, 11 Oct 2011 09:46:13 GMT

Hi ,

I'm working on a file based on a BO extraction where i got 2 fields as date.

Unfortunately when the files are loaded i got values in this way 40817,12354 which is the xls date format, how could I transform them into DD/MM/YYYY?

Thanks in advance

Bruno

Import dates from Excel

m_woolf — Tue, 11 Oct 2011 09:55:29 GMT

In the load script, if the field name is MyDate:

Date(MyDate,'DD/MM/YYYY') as MyDate

Import dates from Excel

Tue, 11 Oct 2011 10:00:34 GMT

use dual in this as:

dual((Month(Date) &'-'&Year(Date)),num(MakeDate(year(Date),month(Date)))) as Monthyear

hope this will help you.

Import dates from Excel

Tue, 11 Oct 2011 10:03:36 GMT

Hi,

You can use the DATE function in the script or front end to correct the field format. For Example:

Script:

DATE("Your Date Field",'DD/MM'YYYY') AS "Date Field"

Front end Expression:

DATE("Your Date Field",'DD/MM'YYYY')

Regards GJ.

Import dates from Excel

Tue, 11 Oct 2011 10:34:59 GMT

Thanks, the problem is that even if correctly converted if I use it in as list i got separate values for the same date.

It seems it is still reading the time part...

Import dates from Excel

m_woolf — Tue, 11 Oct 2011 10:42:36 GMT

Date(floor(MyDate),'DD/MM/YYYY') as MyDate

Import dates from Excel

Tue, 11 Oct 2011 10:57:39 GMT

Thanks a lot...i tried the same with the month and the year but is still duplicating

Import dates from Excel

m_woolf — Tue, 11 Oct 2011 10:59:44 GMT

Tell me how you have the month and year defined in your script.

Import dates from Excel

Tue, 11 Oct 2011 11:02:05 GMT

SET TimeFormat='h.mm.ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h.mm.ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Import dates from Excel

m_woolf — Tue, 11 Oct 2011 11:06:42 GMT

This is just how the formatting is set.

When you say the months and years are duplicating, I assume you are talking about fields in a list box or table.

How are you loading those fields in your script?

If you could attach a sample qvw demonstrating the problem, that would also be helpful.

Import dates from Excel

kamalqlik — Tue, 11 Oct 2011 11:38:52 GMT

DATE("Your field",'DD/MM'YYYY') AS "Field Name"

and if you are getting duplicate value...than you can use the dual()

Re: Import dates from Excel

Tue, 11 Oct 2011 12:36:44 GMT

Hi, as attachment the report i'm working on...

Import dates from Excel

Tue, 11 Oct 2011 13:40:26 GMT

I solve this issue....now I'm working on the traffic light indcator....i would set the indicator on this expression:

count (num att) if codice esito= ok / count (num att)