Need help summarizing a table with duplicates

[GM5]

Hello Community,

I came across the following blocker, customer is sending data to ServiceNow in this format (see image1)

Basically this is an inventory, the idea is to summarize the inventoryCount values and insert them into another table, as you may noticed there are duplicate products which is okay. I tried using glide aggreate but it only supports 1 Group by, if I group by ProductID when inserting on the other table I won't be able to add the product Name and any other column value.

If you run the following script it will Summarize the inventoryCount by Product ID but we still are unable to pass the Product name, because these products are not inserted or exist in servicenow we don't have a "reference" field to tied them and pull their value via dot walking.

var inventory = new GlideAggregate('u_ivm_inventory');
    inventory.addNotNullQuery()
    inventory.groupBy('u_productid');
    inventory.addAggregate('SUM', 'u_inventorycount');

    inventory.query();

    while (inventory.next()) {  
       

        var summary = new GlideRecord('u_ivm_product_display');
        summary.setValue('u_id', inventory.getValue('u_productid'));
        summary.setValue('u_in_stock_count',inventory.getAggregate('SUM', 'u_inventorycount'));
        summary.update();
    }

In other words I'm trying to do an SQL Rollup like on the following images 
The data we get:

Once the transform is compelte, then we trigger a onComplete transform script to build the other table, the idea is to create something like:

With the current servicenow GlideAggregate has been impossible to achieve this.

[Arpan Baishya]

Hi @GM5,

May I ask you the reason behind aggregating the inventoryCount values and putting them in another table? Does it have anything to do with reporting? If so, you could create a report from your existing table and use multiple 'group by' fields.

[GM5]

Hi Arpan,

They idea behind this is that they want to use a list collector variable that shows a list of items, currently if we feed the list collector variable with the actual data they will have duplicates, the idea is they pick one item from the list and later we can extract the amount available and the product ID, this is for a custom integration.

[Arpan Baishya]

Hi @GM5,

Sorry for the late reply. I kind of understand what you're trying to do here. I have a question though. Would it not be best if 

1) the imported data doesn't contain duplicates in the first place AND

2) also doesn't create duplicate data on your target table.

There are ways to accomplish that. Have you considered them?

[GM5]

Hi thanks for Replying,

All your bullet points are valid and were considered initially, however like I mentioned in the beginning of this post the data is coming in that format, is out of our hands at this point and they cannot change it, that's the way it works on their 3rd party app.