Unique_by two columns


#1

Hello! I want to create a new dataset as instructed here:
https://developer.geckoboard.com/api-reference/curl

But I need a combinations of two columns to be unique. In my case

date and zone mus be unique in the dataset. How can I do that?

thanks!


#2

Hi there @hecontreraso,

This is a great question! I wasn’t sure about the answer so I did some testing and you can specify multiple field names as part of unique_by as long as they are string, date or datetime fields and have unique identifiers.

Here’s an example with Node that I put together for testing:

var API_KEY = 'your_api_key';

var gb = require('geckoboard')(
  API_KEY
);

gb.datasets.findOrCreate(
  {
    id: 'sales.by_day',
    fields: {
      quantity: {
        type: 'number',
        name: 'Number of sales'
      },
      gross: {
        type: 'money',
        name: 'Gross value of sales',
        currency_code: "USD"
      },
      date: {
        type: 'date',
        name: 'Date'
      },
      name: {
        type: 'string',
        name: 'Name'
      }
    },
    "unique_by": ["date", "name"]
  },
  function (err, dataset) {
    if (err) {
      console.error(err);
      return;
    }

    dataset.put(
      [
        { date: '2016-01-01', quantity: 819, gross: 2457000, name: "one" },
        { date: '2016-01-02', quantity: 409, gross: 1227000, name: "two" },
        { date: '2016-01-02', quantity: 415, gross: 1229523, name: "two" },
        { date: '2016-01-03', quantity: 164, gross: 492000, name: "three" }
      ],
      function (err) {
        if (err) {
          console.error(err);
          return;
        }

        console.log('Dataset created and data added');
      }
    );
  }
);

The dataset when checked contains only 3 rows, despite sending 4, because one counts as a duplicate under those conditions. Only the later one is kept.

Does this help?

Lisa


#3

Hello Lisa!
Does this dataset allow this entry?

{ date: '2016-01-02', quantity: 409, gross: 1227000, name: "three" },

If yes, then my question is solved (:


#4

It does!

I added { date: ‘2016-01-02’, quantity: 409, gross: 1227000, name: “three” } to my original payload and tested this. It is included in my dataset:

2018-02-01_17-03-57

:slight_smile:

Lisa