Tags: business objects, concat, sap, tables, web intelligence

Here I would like to describe how to get multiple values from rows into one single cell as a comma separated list in 4 steps with 4 variables

I will take standard **eFashion** universe as an example. Let's work on **LINES** and **CATEGORY** objects. Here is the raw list from the universe for **LINES in (Dresses, Jackets, Leather)**:

And what we want here is to be able to list categories as a comma separated list next to each line. So this will be our desired goal:

**STEP #1** (find the maximum category, with respect to each LINE)

Create a variable **[Max Category]**

**Max([Category]) In ([Lines])**

**STEP #2** (concatenate the category values with its previous row value)

Create another variable **[Concat Category]**

**[Category] +", "+ Previous(Self)**

With these variables, it would look like this:

**STEP #3** (get those rows of **[Concat Category]** where the value of the category is maximum for that specific LINE)

Create yet another variable **[Max Concat Category]**

**[Concat Category] Where ([Category]=[Max Category])**

Therefore we have:

If we remove or hide the columns between it would actually look like this:

As you can see, the lists are cumulative and a comma at the end. So our next step will be to eliminate these.

**STEP #4** (display those categories that belong to their Lines, against each of their respective Lines)

Create a final variable **[Category]**

**If(IsNull(Previous([Max Concat Category]));Substr([Max Concat Category];1;Length([Max Concat Category])-2);Substr([Max Concat Category];1;Pos([Max Concat Category];Previous([Max Concat Category]))-3))**

Basically, this formula checks if the previous value of **[Max Concat Category]** is **NULL**.

If so, then it just displays **[Max Concat Category]**

In the above image, this scenario occurs for **LINE = Dresses**. There is no value for **[Max Concat Category]** prior to **LINE = Dresses** and soit is **NULL**.

Otherwise previous row value of **[Max Concat Category]** is removed from the present row value of **[Max Concat Category]**

This scenario occurs for **LINE = Jackets** and **LINE = Leather** (and all the rows that may occur below). This is done with the help of **POS** (for finding the position where the previous value of **[Max Concat Category]** starts), and **SUBSTR** functions to get the desired parts of the text.

So this would reach us our desired outcome:

© 2024 - DJames.net