Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I create qvw doc that load data from excel file. but some data is different format in one field (format date).
How to equalize the format date from this excel file without edit formate date in the excel file.
hope someone help me.
Regards,
Ted
This seems to work:
SET DateFormat='D/M/YYYY';
Table:
LOAD [ENTRANCE DATE],
[EXIT DATE],
company
FROM
ask.xlsx
(ooxml, embedded labels, table is Sheet1);
Hi,
Use Alt()
Syntax with description from Help Menu
alt(case1[ , case2 , case3 , ...] , else)
The alt function returns the first of the parameters
that has a valid number representation. If no such match is found, the last
parameter will be returned. Any number of parameters can be used.
Example:
alt( date#( dat , 'YYYY/MM/DD' ),
date#( dat , 'MM/DD/YYYY' ),
date#( dat , 'MM/DD/YY' ),
'No valid date' )
Will test if the field date contains a date according to any of the three specified date formats.
If so, it will return the original string and a valid number representation of a date.
If no match is found, the text 'No valid date' will be returned (without any valid number representation).
Regards
This seems to work:
SET DateFormat='D/M/YYYY';
Table:
LOAD [ENTRANCE DATE],
[EXIT DATE],
company
FROM
ask.xlsx
(ooxml, embedded labels, table is Sheet1);
You can use two ways to do this.
One is just format it to date and see the different values.
The next way is use conditional conversion based on the deliminter ., that means when it is '-' then one format else the other format.
Dear all,
thank you for your help,
regards,
Ted.