Forum Discussion

AnthonyMcIntyre's avatar
AnthonyMcIntyre
Qrew Member
3 years ago

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
------------------------------
  • 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
    ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain
      Could you elaborate on Jinja Random function ? 

      ------------------------------
      Prashant Maheshwari
      ------------------------------
      • LynnHedegard's avatar
        LynnHedegard
        Quickbase Staff
        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
        ------------------------------
  • 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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      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
      ------------------------------
      • AnthonyMcIntyre's avatar
        AnthonyMcIntyre
        Qrew Member
        Thank you for the formula, it's getting me one step closer to solving this issue!

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