How to get multiple values into one cell.

Thursday, December 17, 2015

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: