topic Re: FirstSortedValue, find last value before (or equal to) 08:00 in QlikView

FirstSortedValue, find last value before (or equal to) 08:00

Mon, 28 Sep 2015 14:16:03 GMT

Hi,

need to find the last value before (or equal to) 08:00 but the below expression does not work properly, any ideas?

Pls find attached example files.

FirstSortedValue({<SettlementTime={'<$(=time(08:00:00:000000))'}>} RunningBalance,-SettlementTime)

Rgds,

Olle

Re: FirstSortedValue, find last value before (or equal to) 08:00

Mon, 28 Sep 2015 14:16:59 GMT

The expression above only picks the very last value for each day, regardless of the time.

//O

Re: FirstSortedValue, find last value before (or equal to) 08:00

Tue, 29 Sep 2015 07:20:12 GMT

any ideas?

Re: FirstSortedValue, find last value before (or equal to) 08:00

Wed, 30 Sep 2015 08:05:50 GMT

anyone?

Re: FirstSortedValue, find last value before (or equal to) 08:00

Digvijay_Singh — Wed, 30 Sep 2015 11:43:06 GMT

Please check if this helps -

Re: FirstSortedValue, find last value before (or equal to) 08:00

jonathandienst — Wed, 30 Sep 2015 12:00:03 GMT

I assume that SettlementTime has no date component (put num(SettlementTime) in a list box - the values should be between 0 and 1). If so, you could try this:

FirstSortedValue({<SettlementTime={"<=$(=time#('08:00:00'))"}>} RunningBalance, -SettlementTime)

Re: FirstSortedValue, find last value before (or equal to) 08:00

Wed, 30 Sep 2015 12:10:19 GMT

Looks good! However not sure how to implement in my rather nested production script, the app provided is only a simplified version.

Re: FirstSortedValue, find last value before (or equal to) 08:00

Kushal_Chawda — Wed, 30 Sep 2015 12:10:47 GMT

try this

=FirstSortedValue({<SettlementTime={"<$(=time#('08:00:00:000000','hh:mm:ss:fffff'))"}>} RunningBalance,-SettlementTime)

Re: FirstSortedValue, find last value before (or equal to) 08:00

Wed, 30 Sep 2015 12:11:39 GMT

This is what I got using num(SettlementTime) in the list box. Weird.....

Re: FirstSortedValue, find last value before (or equal to) 08:00

Wed, 30 Sep 2015 12:12:49 GMT

null values im afraid

Re: FirstSortedValue, find last value before (or equal to) 08:00

Digvijay_Singh — Wed, 30 Sep 2015 13:19:37 GMT

I think Settlement Time is in timestamp format, it might not work in set analysis when we compare it with value having time format(8:00..). that is the reason I added new field converted into time format in script. Also in source data there is 'dot' after seconds, so I think we need to use format 'hh:mm:ss.ffffff' with time# to interpret correctly though haven't checked yet if that makes much difference.