topic AW:Problems importing dates from an excel file in QlikView

Problems importing dates from an excel file

Tue, 20 Jul 2010 11:41:44 GMT

Hello,

I tried to import dates from an excel file to Qlikview using the wizard. However, the wizard loads or shows the dates in a numeric format and not in its original format('MMM-YY'). So, what can I do to solve this problem?

* Is it possible to use a format conversion function to change the numerical format representation to a normal representation?

* Is it possible to sum the number of days(or numeric format for dates) to the '01/01/1900' and get the real date?

I'm using qlikview version 9. Thanks beforehand for your help.

best regards,

david

AW:Problems importing dates from an excel file

brenner_martina — Tue, 20 Jul 2010 11:46:04 GMT

Hi,

one question, is the date in Excel a date or a text? Normally QV import a datefield even as a datefield and that means it is a number. In format numbers you can than change back to a dateformat.

Otherwise QV can interpret even textfields into a datefield using the function date#(...).

Ofcourse you can add dates, sometimes you have to use the function num(...) to change back from a dateformat to a numberformat.

AW:Problems importing dates from an excel file

Tue, 20 Jul 2010 11:58:47 GMT

Hello Martina,

In the excel, the field is a date with the format 'MMM-YY'. For example, in the excel file, the date is 'JAN-10' but when I tried to load to qlikview, it appears 40179. So, I think that this strange number is the number of days between 'JAN-10' and the '01/01/1900' date. So, my question is what shoud I do to interpret or convert these strange numbers into normal dates.

I have loaded dates from another excel files without problems but for this particular excel file, it generates these strange numbers.

Thank you,

Best regards,

David

AW:Problems importing dates from an excel file

Tue, 20 Jul 2010 13:58:46 GMT

Hi,

At the time of loading the Date variable use the following formula

date ( Date , 'MMM-YY' ) as Date

This would load the Date in the format as you need

AW:Problems importing dates from an excel file

Wed, 21 Jul 2010 09:23:20 GMT

Hello,

I solved the problem. I used the following:

month(date(date#('01-1900', 'MM-YYYY') + num#(Period),'MM-YYYY'))

The month function is just for interface purposes of my application. The period is a string variable.

regards,

David Vidal