Discussions

 View Only
  • 1.  Lists and Random Functionality

    Posted 06-23-2022 12:39
    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
    ------------------------------


  • 2.  RE: Lists and Random Functionality

    Posted 06-23-2022 13:47
    I do not believe there is any way to generate random values in QuickBase natively.   You could probably do this using some javascript and a code page, but that's a bit more of an advanced function.

    ------------------------------
    Jeff Peterson
    ------------------------------



  • 3.  RE: Lists and Random Functionality

    Posted 06-23-2022 14:16

    I have this in my bag of tricks to generate a Random number.  If you really cared how random it was you would have to run your own statistical test I suppose. Since it is based on the record ID then you have to decide if that works for your process because of course this pseudo-random number will never change for a particular Record ID


    ToNumber(Right(ToText(Int([Record ID#]*9973/127)),4))



    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 4.  RE: Lists and Random Functionality

    Posted 06-23-2022 14:45
    Thank you for the formula, it's getting me one step closer to solving this issue!

    ------------------------------
    Anthony McIntyre
    ------------------------------



  • 5.  RE: Lists and Random Functionality

    Posted 06-23-2022 16:37
    Edited by Marguerite Keating 06-23-2022 16:38
    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
    ------------------------------



  • 6.  RE: Lists and Random Functionality

    Posted 06-24-2022 12:04
    Could you elaborate on Jinja Random function ? 

    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 7.  RE: Lists and Random Functionality

    Posted 07-14-2022 04:09
    Edited by Lynn Hedegard 07-14-2022 10:15
    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/query

    The 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/records

    The 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
    ------------------------------