Question on transforming a collection

[rchdchiu]

Hello,

Scenario:

- I have a collection with 7 columns, see [collection 1] below
- I would like to transform this collection into [collection 2], see below, I kind of feel I should use GroupBy() function by productType, productBuild and shipToLocation, but I am stuck on how to do the quantity addition and merged comments together.

Example:
- Here is my current source [collection 1]:

ID           Name   Team    productType   productBuild   shipToLocation Quantity
1             Name1 Team1  Type1              Build1              Location1           2
2             Name2 Team2  Type1              Build1              Location1           4
3             Name3 Team3  Type2              Build2              Location2           3
4             Name1 Team1  Type2              Build2              Location2           5
5             Name2 Team2  Type1              Build1              Location2           10

- I would like to create a target [collection 2] like below:

ID    productType   productBuild     shipToLocation  Quantity    Comments

1      Type1             Build1               Location1           6                 Name1>Team1>2 | Name2>Team2>4

2      Type2             Build2               Location2           8                 Name3>Team3>3 | Name1>Team1>5

3      Type1             Build1               Location2           10               Name2>Team2>10

Could anyone give me some suggestions?

Thank you!

[LaurensM · Accepted solution]

Hi @rchdchiu,

You are fully correct in using the GroupBy function in this case! 😊

To achieve the additional columns, we can leverage the AddColumns function and use the grouped table column that GroupBy creates to fetch all of the related row information (called GroupedItemsInfo in the example below).

I will save the table in a new collection but you could use the addcolumns directly in the Items property of a gallery:

ClearCollect(
    colGroupedItems,
    AddColumns(
        GroupBy(
            //Adjust the collection name
            YOUR_COLLECTION_HERE,
            "productType",
            "productBuild",
            "shipToLocation",
            "GroupedItemsInfo"
        ),
        "Quantity",
        Sum(
            GroupedItemsInfo,
            Quantity
        ),
        "Comments",
        Concat(
            GroupedItemsInfo,
            $"{Name}>{Team}>{Quantity}",
            " | "
        )
    )
)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[rchdchiu]

Good day @LaurensM 

Nice to meet you here again and thanks so much for your answers. Sorry for the late response since I tested and it is working! See screenshot below:

For sure I will accept it as solution and give it thumbs up

Question as always since this is good time to learn from you. 

        GroupBy(
            //Adjust the collection name
            YOUR_COLLECTION_HERE,
            "productType",
            "productBuild",
            "shipToLocation",
            "GroupedItemsInfo"
        ),

As you showed me before, the above scripting will generate grouped table and [GroupedItemsInfo] will store the grouped records. 

So the AddColumns() function will add columns on the higher level grouped table and Sum() only sum the lower level Quantity within each group, is that correct? It is because when I first read your scripting, I was wondering how it is smart and won't sum the quantity between groups. 

        Concat(
            GroupedItemsInfo,
            $"{Name}>{Team}>{Quantity}",
            " | "
        )

I was not sure what is the function of $ sign, could you give me some explanation?

Many thanks

Rich

[rchdchiu]

Good day  @LaurensM 

Nice to meet you here again and thanks so much for your answers. Sorry for the late response since I tested and it is working! See screenshot below:

For sure I will accept it as solution and give it thumbs up

Question as always since this is good time to learn from you. 

        GroupBy(
            //Adjust the collection name
            YOUR_COLLECTION_HERE,
            "productType",
            "productBuild",
            "shipToLocation",
            "GroupedItemsInfo"
        ),

As you showed me before, the above scripting will generate grouped table and [GroupedItemsInfo] will store the grouped records. 

So the AddColumns() function will add columns on the higher level grouped table and Sum() only sum the lower level Quantity within each group, is that correct? It is because when I first read your scripting, I was wondering how it is smart and won't sum the quantity between groups. 

        Concat(
            GroupedItemsInfo,
            $"{Name}>{Team}>{Quantity}",
            " | "
        )

I was not sure what is the function of $ sign, could you give me some explanation?

Many thanks

[LaurensM]

Hi @rchdchiu,

Nice to meet you on the forum again as well! No need to apologize - feel free to respond at any time that is most convenient to you. 😉

To answer your questions.

(1) Grouped table & child items:

The GroupBy() function will add an additional column to the output table containing all of the 'grouped child items'- the new column name is the last parameter of the function. In other words, with my code you will only display items that have a distinct productType, productBuild & shipToLocation combination. The remaining columns of the grouped items are then saved to the new column called 'GroupedItemsInfo'.

To make it less abstract, here is a quick sketch of how the output (before AddColumns) looks like:

productType	productBuild	shipToLocation	GroupedItemsInfo
Type1	Build1	Location1	[     {         name: "name1",         team: "team1",         quantity: 2     },     {         name: "name2",         team: "team2",         quantity: 4     } ]
Type2	Build2	Location2	[     {         name: "name3",         team: "team3",         quantity: 3     },     {         name: "name1",         team: "team1",         quantity: 5     } ]
Type1	Build1	Location2	[     {         name: "name2",         team: "team2",         quantity: 10     } ]

The AddColumns() & Sum() function will only count the quantity of that particular 'GroupedItemInfo' column.

(2) String Interpolation

The dollar sign before a text value allows you to concatenate text (but also variables, control values...) in a very easy manner. In essence, it is very similar to using a Concatenate() function or using the & operator. 

String interpolation is my preferred way of concatenating text due to the high readability and low code density (especially with large concatenations). Below all 3 options to concatenate the Comments string:

//String interpolation
$"{Name}>{Team}>{Quantity}"

//& operator
Name & ">" & Team & ">" & Quantity

//Concatenate function
Concatenate(Name, ">", Team, ">", Quantity)

Although differently written, they achieve the same result - feel free to pick one of the above. To keep your code clean and simple, stick to 1 variant within your project.

I hope this helps!