Extracting and re importing a date cube

Adam Elvin

Hello, I am trying to build a solution to record guest stays at a ded and breakfast, the idea is to have a number of cubes to data entry some details about the stay, i.e. date cubes (no time entity though) by guest and property to enter the check in and checkout dates, (the number of nights will be calculated in a process. A double cube to enter the amount they are paying, ( a day rate will be calculated by dividing by the number of nights).

 

I want to build a process where i can then translate in to anotheg cube that has Guest, Property, time entities taking the daily rate and posting it against the check in date (and ultimately for all consecutive dates of their stay, i.e. if they stay for therr nights post to the checkin date and the next two dates, but first things first!)

 

This is my layout in the extract tab of a process 

 

This is the csv extract

 

I then try and read it into a cube but find i can't and I think it is for two reasons, firstly because the date field has a time stamp added on to it, i.e. 29/04/2016 00:00:00, and secondly and probably most likely is because it is coming from a text file and the date field is text and not a date.  I tested this by creating a csv file in excel and entering the data and formatting the date column as a date before creating the csv file and this allowed the data to go into the cube.

 

So my question is how am I able to extract a layout into a file mainting the date information be retained in a date format that BOARD can understand when it is reimported.

Helmut Heimann

Hi Adam Elvin,

 

although I'm pretty sure that BOARD will interpret any date (with or without a timestamp) correctly if it's mapped to the day entity, I'd propose using the ETL to extract day, month and year from the textstring in the correct order (using "left", "mid" and "right")--as I suspect the problem you are describing results from the date format you are using in your csv compared to the format BOARD is using.

And (unlike an SQL datareader does) the ASCII datareader offers no option concerning the date format.

 

Hope this helps.

 

Kind Regards,

Helmut

Unknown

Hi Adam Elvin,

   I think Helmut Heimann is right that reformatting the date should do the trick. Here are the steps to figure out exactly what date code format to use

• Open the Database-->Time Range feature
  
• Click Custom Entities 
  
• For the grain of the cube you are trying to load look at the members of that specific entity (i.e. Month)
  
• The listing will show the code and description. If you can reformat your ASCII date into the code (left column),  you should be able to load the data into a date at the same grain.
  

Brendan Broughton

Hi Adam Elvin, ETL should also allow you to use the excel date formulas Year(), Month(), Day() and Text(Celref,"dateformat") to convert back to a BOARD formatted date.