Create date field in IDT 4.0 from Excel spreadsheet column

[Former Member]

Hi Experts

I have an Excel document with a column called Date. The column is formatted as a text
field in the Excel document and the format of the date is YYYYMMDD (ex.
20120423). On top of this Excel spreadsheet I am trying to create a universe
using IDT 4.0. In the universe a have created a dimension with the format Date
(not DateTime). If I assign the Excel date column to the dimension Date I get
an error message saying that the “return data type is not correct”.

I tried the function to_date, but then I get the error message that it is impossible to
execute the business layer.

What code do I put in the Select statement for my dimension Date to populate it with my
Excel date column?  

Kind regards

Erik

[Former Member · Accepted solution]

Hi Erik,

Have you got access to the data sources (excel sheet). in that case you can apply the following formula on a column which is the copy of the column you want to be "the date."

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)); A1 being the column you want to change.

Otherwise there is no option to change it in Universe.

Or you can then change it in the WEBI reports using the FIRMAT NUMBER or FORMAT DATE functions.