Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi evrybody,
I am facing a strange issue, I should nromally not (according to myself)
I have dates in XL File (date format cells)
When I load it to QV i get numbers (OK) but I can't tranform it under date format.
I will appreciate your knowledge to solve this
here are the files
bedt regards
juju
Hi,
Num# will convert string to number.
Regards
ASHFAQ
Hi Kush
Thank you taht works.
Just explain why Trilm is needed because there is apparently no blank
Juju
Yes sorry, I clicked the wrong name
Yes I know and normally date#() will interpret as well date and transform it
As my cells are in XL date format it should be directly interpreted by date
Juju
What I understood using trim is that when you have numbers as text, you can simply use trim function to convert to actual number , no need to use num#
your month data is in text format , that why we need to use num#() and not the date#
date#(expression [ , format-code ])
The date# function evaluates the expression as a date according to the string given as format-code. If the format code is omitted, the default date format set in the operating system is used.
Examples:
The examples below assume the two following operating system settings:
Default setting 1 | Default setting 2 | |
Date format | YY-MM-DD | M/D/YY |
date#( A ) where A=8/6/97 returns:
Setting 1 | Setting 2 | |
String | 8/6/97 | 8/6/97 |
Number | - | 35648 |
date#( A, 'YYYY.MM.DD' ) where A=1997.08.06 returns:
num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])
The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.
Examples:
The examples below assume the two following operating system settings:
Default setting 1 | Default setting 2 | |
Number format | # ##0,# | #,##0.# |
num#( A, '#' ) where A=35,648.375 returns:
Setting 1 | Setting 2 | |
String | 35,648.375 | 35648.375 |
Number | - | 35648.375 |
num#( A, '#.#', '.' , ',') where A=35,648.375 returns:
Setting 1 | Setting 2 | |
String | 35,648.375 | 35,648.375 |
Number | 35648.375 | 35648.375 |
num#( A, '#.#',',','.' ) where A=35648.375 returns:
Setting 1 | Setting 2 | |
String | 35648.375 | 35648.375 |
Number | 35648375 | 35648375 |
num#( A, 'abc#,#' ) where A=abc123,4 returns:
Setting 1 | Setting 2 | |
String | abc123,4 | abc123,4 |
Number | 123.4 | 1234 |
Yes Avinash,
I read that too but i just don't understand why date# does not work in my case
01/04/12 is a date format
The date# function evaluates the expression as a date according to the string given as format-code.
so date#(Month, 'DD/MM/YY') should return a date
and date(date#(Month, 'DD/MM/YY'), 'DD.MM.YYYY') should return 01.04.2012
so is my question : why this does not work ?
Juju
At what point of time in your script execution are you trying to parse the date?
Could you post your script?
As I said above, the CROSSTABLE will transform all column labels to text, and the column label is a textual number like 41030 when read from excel. so trying to interpret it with a date format will not work.
Swuehl
That's an odd behaviour of the CROSSTABLE LOAD prefix, columns labels will always be transformed to text values.
that's the point I missed.
Thank you
Juju