Forum Discussion

Qrew Cadet

3 years ago

GROUPBY Function now available?

I visited the QuickBase article (About Jinja) and read that "Pipelines is currently supporting Jinja 2.11.2, for various formatting within pipeline steps". That webpage also also provides a link for ...

DougHenning1

Community Manager

Hey Shane, here's a quick example using the Text step:

{% set users = [
  {
    "id": 1,
   "name": "bob",
  "city": "Boston"
  },
  {
    "id": 2,
   "name": "jane",
  "city": "Boston"
  },
  {
    "id": 3,
   "name": "george",
  "city": "Baton Rouge"
  }
] %}
{{ users | groupby("city") }}

The results in the activity log:

text:"[(u'Baton Rouge', [{u'city': u'Baton Rouge', u'id': 3, u'name': u'george'}]), (u'Boston', [{u'city': u'Boston', u'id': 1, u'name': u'bob'}, {u'city': u'Boston', u'id': 2, u'name': u'jane'}])]"

As you can see, the list is now grouped by the city, with each city results in a nested list.

The example on the Jinja website works to parse this sample data. Remove the last line from the Jinja above and add this to the end:

<ul>{% for city, items in users|groupby("city") -%}
  <li>{{ city }}
    <ul>{% for user in items -%}
      <li>{{ user.name }}
    {% endfor -%}</ul>
  </li>
{% endfor -%}</ul>

The outer loop references the first value as 'city' and the second value as 'items' which is the list for that city. The inner loop iterates the items for that city.

Hope that helps!

------------------------------
Doug Henning
------------------------------

ShaneMiller1

Qrew Cadet

3 years ago

I need to study Jinja2. That's Interesting. So I have some prior knowledge of SQL(though rusty), which is where I am pulling this reference from, so excuse my ignorance, but in SQL, if a GroupBy statement was made for [city], the result would be:
count id | count name | city unique
2 | 2 | Boston
1 | 1 | Baton Rouge

However, from my understanding of your post, this is not how GroupBy works in Quickbase's Pipelines?

The reason I ask is because my pipeline is bringing in a bunch of records(claims) whom have duplicate [Member_ID] fields, however, I only want to create a record for 1 of those duplicate records. So I was thinking I could maybe use a GroupBy [Member_ID]. I've been scouring the internet for tricks on how to do this. I thought the answer was either the use of:
{{ ........ |unique|list }} or this GroupBy feature. Welp, Back down the rabbit hole I go

------------------------------
Shane Miller
------------------------------

DougHenning1

Community Manager

3 years ago

I think you can use the groupby(). It's returning a list like this:
[ 10, [{id=1, ...},{id=2}], 20, [{id=3}]

Updated test data to include "memberId":

[
  {
    "id": 1,
   "memberId": 10,
   "name": "bob",
  "city": "Boston"
  },
  {
    "id": 2,
   "memberId": 20,
   "name": "jane",
  "city": "Boston"
  },
  {
    "id": 3,
   "memberId": 30,
   "name": "george",
  "city": "Baton Rouge"
  },
{
  "id": 4,
   "memberId": 10,
   "name": "bob",
  "city": "Boston"
  }
]

OPTION A
If you just need the unique memberIds:

{{ users | map(attribute='memberId') | unique | list }}

Results:

[10, 20, 30]

OPTION B
If you need the record data and not just the ids:

{% for memberId, user in users | groupby('memberId') -%}
{{ user[0] }}
{% endfor -%}

The 'user[0]' is to reference the first object in the list for that memberId since you don't need them all.

Results:

{'id': 1, 'memberId': 10, 'name': 'bob', 'city': 'Boston'}
{'id': 2, 'memberId': 20, 'name': 'jane', 'city': 'Boston'}
{'id': 3, 'memberId': 30, 'name': 'george', 'city': 'Baton Rouge'}

Notice record with id=4 does not display.

UPDATE
Turns out unique takes a parameter for an attribute, so for Option B you could do this instead of the for loop to get the unique records:

{{ users | unique(false, attribute='memberId') | list }}

Hope that helps!

------------------------------
Doug Henning
------------------------------