topic Set Analysis rounding numbers - Remove Milliseconds during Script load in QlikView

Set Analysis rounding numbers - Remove Milliseconds during Script load

Mon, 16 Jan 2012 17:34:01 GMT

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

];

swuehl — Mon, 16 Jan 2012 17:44:00 GMT

I think you could truncate the milliseconds like this:

SET TimeFormat='h:mm:ss TT';

StaticData:

NOCONCATENATE

LOAD *,

floor(StartDate)+Time#(Time(StartDate)) as StartDate2

INLINE

[ StartDate, Amount

40693.999998611, 1000

40693, 10

40694, 500

40695,200

];

StartDate2 should only hold time up to the seconds now. And in your example, the sum seems to be correct and will return 500.

Hope this helps,

Stefan

Mon, 16 Jan 2012 20:00:11 GMT

thank you! it works great!!