Erroneous dataflow from text to single type

Unknown
Unknown Active Partner
in Platform

Dear all,

 

While doing some tests, I came across a problem with a dataflow from text to single type in BOARD 10.3 (Didn't try in older versions). Let me explain the whole business case.

 

1) I pull in a "creation date" column from an Excel file and apply an ETL function on it during the data reader. The ETL function is "LEFT(C12,4)&MID(C12,6,2)&MID(C12,9,2)" and allows me to store the YYYYMMDD of a timeformat. Please find row 12 of following example:  

 

image
image

 

2) This new cube "Open Date Text" is a text cube (two dimensions: Day and TicketNumber) and I want to change the format to single, to be able to change the date at a later stage to a numeric value and compare with the close date.

 

image


3) However when running this dataflow, I get strange results, leading often to a day off, earlier or later.

 

image

 

Anyone in the community that already came across similar situations. I would love not to change anything in Excel, and handle the whole data manipulation flow inside BOARD. So please advise in case you know a workaround.

 

All the best,
Jonathan 

Tagged:

Answers

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers
    edited March 2020

    Hi,

     

    it's not a problem from 10.3 or a problem from BOARD Software. It's more or less a Single/double problem (in general a floating point problem).

    Single only guarantee for an precision of 7 digits, so some 8 digit numbers may not be saved correctly:

    float keyword (C# Reference) | Microsoft Docs 

     

    So if single isn't accurate, just use double:

    image

     

    Regards

    Björn

  • Unknown
    Unknown Active Partner

    Thanks Bjorn. It works like a charm by changing the datatype to double.

     

    Best,
    Jonathan

Categories