Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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
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