Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]How to get random number of records
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]How to get random number of records
 Login/Join
 
Member
posted
Hello,
I am trying to figure out how to get random number of records to show up in the report based on another report.
For example:
I have a summary report that tells me the count of records for each field.
I then calculate a smaller percentage of those numbers and call it random. I then need to pass this number into a detail
report, but every time I run the report, I need to get the detail report for the number that was passed from the summary report and every time it will have to be different(or random) records.
Here is my code example using movies file:
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

TABLE FILE ibisamp/movies
SUM 
CNT.TITLE
COMPUTE 50_percent/D12.2%=50 ;
COMPUTE random_number/D12=( 50_percent * CNT.TITLE )/100 ;
BY CATEGORY
WHERE RATING EQ 'NR';
WHERE CATEGORY NE 'MYSTERY';
ON TABLE PCHOLD FORMAT XLSX OPEN
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='Summary', SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
ENDSTYLE
END

-RUN

TABLE FILE ibisamp/movies
PRINT TITLE
BY RATING NOPRINT
BY CATEGORY
WHERE RATING EQ 'NR';
WHERE CATEGORY NE 'MYSTERY';
ON TABLE SET COMPOUND BYTOC 
ON TABLE PCHOLD FORMAT XLSX CLOSE
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='', SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
ENDSTYLE
END

-RUN


The summary tab contains Category, Title Count, percentage and random_number.
So for example for Children category I have 5 title counts. If I take 50% of number 5 and round it up to the nearest integer I get 3(2.5 rounded up).
I now need in the detail report tab for Children Category to show 3 Titles. But every time I run it I need to show RANDOM 3 titles.
And so on for all other categories. For Classic Category I need to show 5 random titles in the detail tab. And so on for each of the Rating. In this example I am limiting to one rating, but I will need to show all of the Ratings in both the summary report and each tab will be its own Rating(BYTOC).

Thank you.

This message has been edited. Last edited by: nickz,
 
Posts: 12 | Registered: September 13, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
 
Posts: 1852 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
Am I understanding correctly that you mainly want a random read from the database (movies in this case)? The more titles you have, the more random records you want?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 439 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
No, not exactly.
I want to get the real count from the database, then take a smaller percentage of that count and use that number as a random sample of records.
So in my example the real count of Category Children is 5, the real count of Category Classic is 10 and the real count of Train/EX is 5. So the smaller percentage of lets say 50% would be 3,5 and 3 if rounded up to the nearest integer. So those are the random number of titles that I want to see on the detail tab. 3 random titles for children, 5 random titles for Classic and 3 random titles for Train/Ex.
 
Posts: 12 | Registered: September 13, 2019Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi nickz,

Not sure if this will help, but this is how I used to pull extracts of random records.

Assign a random number to each record ... hold the records ... resort the file by the random number ... then extract however many records for your random list.

Here's a sample using the CAR file ...

-*****************************************
-**     THIS USES FUNCTION = RDUNIF     **
-*****************************************
-* CREATE HOLD FILE FROM CAR
-*
-DEFAULTH &MAX_RAND = 3;
-*
TABLE FILE CAR
PRINT
COMPUTE CNTR/I5 = CNTR + 1;
COMPUTE RAND/D12.8 WITH CAR = RDUNIF(RAND);
BY CAR
ON TABLE HOLD AS HLD1
END
-*
TABLE FILE HLD1
PRINT *
END
-*
TABLE FILE HLD1
PRINT
COMPUTE RAND_CNTR/I5 = RAND_CNTR + 1;
CAR
CNTR
BY RAND
ON TABLE HOLD AS HLD2
END
-*
TABLE FILE HLD2
PRINT *
END
-*
TABLE FILE HLD2
PRINT *
WHERE RAND_CNTR LE &MAX_RAND
END
-EXIT


WebFocus 8.201M, Windows, App Studio
 
Posts: 225 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Interesting puzzle. Try the following code, see if it does what you are looking for.
-DEFAULT &PCT = 50;
 TABLE FILE ibisamp/movies
      SUM 
  COMPUTE Limit/P9 = &PCT * CNT.TITLE / 100;
       BY RATING 
       BY CATEGORY 
    PRINT TITLE
  COMPUTE Random/P9.8 = RDUNIF(Random);
       BY RATING 
       BY CATEGORY
 BY TOTAL Random NOPRINT
 WHERE CATEGORY NE 'MYSTERY';
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS holdmovie
 END

 TABLE FILE holdmovie
   PRINT TITLE 
         Random NOPRINT
         Limit NOPRINT 
 COMPUTE Counter/P9 = IF LAST CATEGORY EQ CATEGORY THEN Counter + 1 ELSE 1; NOPRINT
      BY RATING NOPRINT
      BY CATEGORY
 WHERE TOTAL Counter LE Limit
 ON TABLE SET COMPOUND BYTOC 
 ON TABLE PCHOLD FORMAT XLSX
 END 


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 179 | Registered: October 19, 2010Reply With QuoteReport This Post
Member
posted Hide Post
@dbeagan,
This is absolutely perfect.

Thank you so much.
 
Posts: 12 | Registered: September 13, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]How to get random number of records

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.