Palantir typescript order rows and filter

user_14820295 0
Viewed 75

I have already created a function that allows me to have a table obtained through groupby and aggregation function. Since the column I have grouped has so many values, I would like to sort descending the rows (by the "percentage" column, created by division of 2 columns) and then keep only the first 5 rows.

This I would like to do in TypeScript, perhaps by appending it under the groupby function, if possible.

Thank u!!!

Output example:

column1 percentage
1 0.75
2 0.65
3 0.63
4 0.61
5 0.55

Code example:

export class funzione1 {
     @Function()
        public async test1(object1: ObjectSet<object1>):
            Promise<TwoDimensionalAggregation<string>> {
                const numerators = await object1.groupBy(e => e.column1.topValues())
                                        .sum(e => e.column2);
                const denominators = await object1.groupBy(e => e.column1.topValues())
                                        .sum(e => e.column3);
        return this.divideTwoDimensional(numerators, denominators);
    }

    private divideTwoDimensional(numerators:TwoDimensionalAggregation<string>,
                             denominators: TwoDimensionalAggregation<string>):
                             TwoDimensionalAggregation<string> {

        const percentage = numerators.buckets.map((bucket, i) => {
           const numerator = bucket.value;
           const denominator = denominators.buckets[i].value;
            if (denominator === 0) {
                return { key: bucket.key, value: 0 };
            }
            return { key: bucket.key, value: numerator / denominator }
        });

        return { buckets: percentage };
    }
foundry-workshop react-typescript palantir-foundry typescript
2 Answers

You can do this with the percentage list; it's simply an array of objects with a key/value structure like:

{
  key: "bucketKey",
  value: .5
}

So you can sort this list of objects as you would any other list of objects.

Similarly, once sorted, you can use the native array slice method to return the first n elements of the array.

If you do this between creating the percentage array and the return statement where you add the buckets object, then you should get the desired result.

user_143208250

You can sort the buckets by their percentage, using the JavaScript .sort() function with a custom comparator. For example, here the helper function sortBucketsByValue2D uses this to sort the buckets by their percentage value:

import { Function, TwoDimensionalAggregation, BucketKey, BucketValue } from "@foundry/functions-api";
import { Objects, object1 } from "@foundry/ontology-api";

export class funzione1 {
    @Function()
    public async test1(object1: ObjectSet<object1>):
        Promise<TwoDimensionalAggregation<string>> {
        const numerators = await object1.groupBy(e => e.column1.topValues())
            .sum(e => e.column2);
        const denominators = await object1.groupBy(e => e.column1.topValues())
            .sum(e => e.column3);
        
        const withPercentage = divideTwoDimensional(numerators, denominators);
        return sortBucketsByValue2D(withPercentage, 'desc');
    }
}

const divideTwoDimensional = (
    numerators: TwoDimensionalAggregation<string>,
    denominators: TwoDimensionalAggregation<string>
): TwoDimensionalAggregation<string> => {
    const percentage = numerators.buckets.map((bucket, i) => {
        const numerator = bucket.value;
        const denominator = denominators.buckets[i].value;
        if (denominator === 0) {
            return { key: bucket.key, value: 0 };
        }
        return { key: bucket.key, value: numerator / denominator }
    });

    return { buckets: percentage };
}

/**
 * Sort buckets of a 2D aggregation by their value in the specified order
 * 
 * Example input 1:
 * { buckets: [
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: 456 },
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: 123 },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: 789 },
 * ]}
 * 
 * Example output 1:
 * { buckets: [
 *   { key: { min: "2021-01-01", max: "2021-12-31" }, value: 123 },
 *   { key: { min: "2022-01-01", max: "2022-12-31" }, value: 456 },
 *   { key: { min: "2023-01-01", max: "2023-12-31" }, value: 789 },
 * ]}
 * 
 * Example input 2:
 * { buckets: [
 *   { key: 17, value: 456 },
 *   { key: 21, value: 123 },
 *   { key: 23, value: 789 },
 * ]}
 * 
 * Example output 2:
 * { buckets: [
 *   { key: 21, value: 123 },
 *   { key: 17, value: 456 },
 *   { key: 23, value: 789 },
 * ]}
 */
function sortBucketsByValue2D<K extends BucketKey, V extends BucketValue>(
    buckets: TwoDimensionalAggregation<K, V>,
    order: 'asc' | 'desc' = 'asc'
): TwoDimensionalAggregation<K, V> {
    return {
        // See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sort
        buckets: buckets.buckets.sort(({ value: v1 }, { value: v2 }) => {
            // These are be either numbers, timestamps or localdates which can be compared like this
            return (order === 'desc' ? -1 : 1) * (v1.valueOf() - v2.valueOf());
        }),
    };
}
user_53522270
Related