I am trying to figure out how to get random number of records to show up in the report based on another report.
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,
WebFOCUS 8206, Unix, Windows
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.
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.
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
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
This is absolutely perfect.
Thank you so much.
|Powered by Social Strata|