2 Replies Latest reply: Jan 16, 2012 3:00 PM by Eduardo Herrera RSS

    Set Analysis rounding numbers - Remove Milliseconds during Script load

      hello,

       

      I've been looking for a solution to remove milliseconds during a load script.

       

      something like this in SQL Server:

      DATEADD(MILLISECOND, DATEPART(ms, dateValue1)*-1, dateValue1) TruncatedDate

       

      I saw a post to truncate seconds using the floor, but I couldn't figure it out to remove milliseconds.

       

      basically I am trying to solve a problem where Set Analysis is rounding numbers, or dates if you like.

       

      on my attached example: I have a particular number of 40693.999998611 that translates to this date: '2011-05-30 23:59:59.880'

       

       

      with a very simple Set Analysis QV is picking that record and it shouldn't

      =SUM(

                {

                          $<

                                    StartDate = {">=40694<40695"}

                          >

                }

       

                Amount

                )

      this will produce: 1500

       

       

       

      StaticData:

                NOCONCATENATE

                LOAD * INLINE

                [          StartDate, Amount

                          40693.999998611, 1000

                          40693, 10

                          40694, 500

                          40695,200

                ];