Here is a Pipeline to select a given percent of records from a table. I use 2 "Make Request" steps, as they are very efficient at traversing a VERY large data sets and performing data transformations on the data as it moves through the pipeline. I can filter the source records by any field. In this example, I'm filtering on "Dimension_01" (FID[11]).
STEP A) This Make Request step scans the entire data set from a source table.
The URL is;
https://api.quickbase.com/v1/records/queryThe Body is;
{
{% set myData_Set_01 = "ttttttttt" %}.
"from": "{{myData_Set_01}}",
"select":[3],
{# Dimension 01 #}
"where": " {11.EX.'A'}OR{11.EX.'B'}OR{11.EX.'C'}OR{11.EX.'D'}OR{11.EX.'E'}"
}
* replace "ttttttttt" with the table ID for your source table
STEP B) This Make Request step is where the random selection is performed.
The URL is;
https://api.quickbase.com/v1/recordsThe Body is;
{
{% set myData_Set_01 = "ttttttttt" %}
"to": "{{myData_Set_01}}",
"data":[
{% set mySamplePerc = 20 | int() %}
{% set glob_var = namespace(cnt=0) %}
{% set myCNT = 0 | int() %}
{% for myCurrRecord in (a.json.data) %}
{
{% set myRandNum = range(1, 100) | random | int() %}
"10":{
{# Global Cnt #}
"value": "{{glob_var.cnt}}"
},
"3": {
{# Record ID #}
"value": "{{myCurrRecord['3'].value}}"
},
"8":{
{# The Random Number.
Placed in the table only for test validation #}
"value": "{{myRandNum}}"
},
"9":{
{# Selected Checkbox #}
{% if (myRandNum <= mySamplePerc) and (glob_var.cnt < 20) %}
"value": "{{1}}"
{# increment count by 1 #}
{% set glob_var.cnt = glob_var.cnt + 1 %}
{% else %}
"value": "{{0}}"
{% endif %}
}
}
{% if loop.last == false %},{% endif %}
{%- endfor %}
]
}
Here is a view of the first few rows of the test table.
- The random records selected are designated via the "Selected_01" checkbox.
- myGlobalCnt is a running count of the number of selected records.
- The Dimension_01 field is there to show how you can filter the rows in STEPA
------------------------------
Lynn Hedegard
Quickbase
------------------------------
Original Message:
Sent: 06-24-2022 12:04
From: Prashant Maheshwari
Subject: Lists and Random Functionality
Could you elaborate on Jinja Random function ?
------------------------------
Prashant Maheshwari
Original Message:
Sent: 06-23-2022 16:37
From: Marguerite Keating
Subject: Lists and Random Functionality
Mark and Jeff both mentioned some potential solutions. Agreed there isn't an easy answer to this specific issue in QB.
Another option would be to use the Jinja Random function in a pipeline based off the Rec ID#, limit the result to 1k records, and then have the pipeline mark those 1000 random records with a checkbox. That way the report could be filtered to only show 1k records and be random.
------------------------------
Marguerite Keating
Quickbase Developer
Synctivate Inc
marguerite@synctivate.com
www.Synctivate.com
Original Message:
Sent: 06-23-2022 12:39
From: Anthony McIntyre
Subject: Lists and Random Functionality
Hi everyone, I am new to QB so these questions could have simple solutions so I figured I would post them. I am currently uploading 100K+ in entries from excel and want to select a random 1000 from the data set. Is there a way to do that? I looked into the report formulas and pipelines and nothing stood out, but doesn't mean it's not there. 2nd - If not, I know how to randomize it based off sorting the data a particular way, but didn't know if I am able to limit the output of the table to only 1000 rows? Either would work, but a random function would be more idea.
Thank you
Anthony
------------------------------
Anthony McIntyre
------------------------------