Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
hugmarcel
Specialist
Specialist

Mathematical problem for clever clocks.

Hi

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

2 Replies
swuehl
MVP
MVP

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;

hugmarcel
Specialist
Specialist
Author

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