Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Import dates from Excel

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

12 Replies
mwoolf
Honored Contributor II

Import dates from Excel

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

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

Not applicable

Import dates from Excel

use dual in this as:

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

hope this will help you.

Not applicable

Import dates from Excel

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.

Not applicable

Import dates from Excel

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...

mwoolf
Honored Contributor II

Import dates from Excel

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

Not applicable

Import dates from Excel

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

mwoolf
Honored Contributor II

Import dates from Excel

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

Not applicable

Import dates from Excel

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';

mwoolf
Honored Contributor II

Import dates from Excel

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.

Community Browser