Using array method to calculate arraySizeMethod

[BrianHung]

I'm trying to implement the UNIQUE array function but am running into a concept problem of how to implement an arraySizeMethod for it.

The confusion is that the array size for UNIQUE depends on the arguments, and as a result is variable. For example, [1, 1, 2] would have dimensions {width: 1, height: 2} while [1, 2, 3] would have {width: 1, height 3}.

I would like to re-use the method that I implemented for UNIQUE to calculate the arraySizeMethod for the function; is that possible?

So far I have

  public unique(ast: ProcedureAst, state: InterpreterState): InterpreterValue {
    return this.runFunction(
      ast.args,
      state,
      this.metadata("UNIQUE"),
      (rangeValues: SimpleRangeValue, byColumn: boolean, exactlyOnce: boolean) => {
        if (rangeValues.width() > 1 && rangeValues.height() > 1) {
          return new CellError(ErrorType.NA, ErrorMessage.WrongDimension);
        }

        // Use a map to keep insertion order.
        const uniques = new Map<string, { count: number; value: any[] }>();

        if (byColumn) {
          for (let i = 0; i < rangeValues.width(); i++) {
            const key = rangeValues.data.map(row => row[i].toString()).toString();
            const uniq = uniques.get(key);
            if (uniq) {
              uniq.count++;
            } else {
              uniques.set(key, {
                count: 1,
                value: rangeValues.data.map(row => row[i]),
              });
            }
          }
        } else {
          for (let i = 0; i < rangeValues.height(); i++) {
            const key = rangeValues.data[i].map(d => d.toString()).toString();
            const uniq = uniques.get(key);
            if (uniq) {
              uniq.count++;
            } else {
              uniques.set(key, {
                count: 1,
                value: [...rangeValues.data[i]],
              });
            }
          }
        }

        if (exactlyOnce) {
          uniques.forEach(({ count }, key) => count > 1 && uniques.delete(key));
        }

        const numCols = byColumn ? uniques.size : rangeValues.width();
        const numRows = byColumn ? rangeValues.height() : uniques.size;

        const values = Array.from(Array(numRows), () => new Array(numCols));

        Array.from(uniques.entries()).forEach(([k, { value }], i) => {
          if (byColumn) {
            value.forEach((v, j) => {
              values[i][j] = v;
            });
          } else {
            values[i] = value;
          }
        });

        return SimpleRangeValue.onlyValues(values);
      }
    );
  }

[sequba]

Hi @BrianHung, sorry for such a late reply.

I would like to re-use the method that I implemented for UNIQUE to calculate the arraySizeMethod for the function; is that possible?

Unfortunately, it is not possible because arraySizeMethod is called before the evaluation of the function arguments, so it cannot depend on their values, only their count, size, and other metadata. It is a general limitation of the HyperFormula engine.

The exact size of the result of the function UNIQUE depends on the argument values, but the arraySizeMethod could return the size of the input range, which is an upper bound on the size of the output.

For reference, you can look at the arraySizeMethod for the function FILTER, which is similar to UNIQUE in this sense.

Hope this answer will be helpful for you.