topic Re: Mathematical problem for clever clocks. in QlikView

Mathematical problem for clever clocks.

hugmarcel — Thu, 06 Sep 2018 19:52:15 GMT

I have a table of service requests which are created constantly and whole day long with unique timestamps (SR_TIME).
My task now is to select 50% of all requests equally distributed over the whole day.

Solution:
LOAD SR WHERE MOD(X,2) = 1; // select every 2nd second.
LOAD SR, AUTONUMBER(SR_TIME) AS X

RESIDENT SRs
ORDER BY SR_TIME;

Is there some "general formula" or load statement (single statement, no loops) which will select 60% or ANY OTHER percentage
rate of all records equally distributed over the whole day?

Thank you! Marcel

Re: Mathematical problem for clever clocks.

swuehl — Thu, 06 Sep 2018 20:25:45 GMT

It depends what you expect for 'equally distributed'.

Are your input records for the calls already equally distributed, i.e. they show the same time difference?

If not, your Solution will not change the distribution, i.e. it will just return every second call, and if the calls show a accumulation around noon, your sample distribution will show the same.

But if your input records are already equally distributed according your requirements, and you have a large number of calls per time bucket of interest (minute / hour etc), you maybe can create a sample like this:

Let vSample% = 0.6;

INPUT:

LOAD Recno() as TimeID, Time(Recno()/100) as Time

AutoGenerate 100;

LOAD TimeID, Time, Rowno() as NoSamples

Resident INPUT

Where TimeID *$(vSample%)> Rangesum(Peek('NoSamples'));

DROP TABLE INPUT;

Re: Mathematical problem for clever clocks.

hugmarcel — Thu, 06 Sep 2018 20:34:32 GMT

Yes

records are already equally distributed and we have a large number of calls per time bucket of interest (minute / hour etc).

I will have the time to check your solution tomorrow!

Thank you! Marcel