Find value with most occurences for a given element

[MarenC]

Hi,

In the following example I have source data as follows, let us say in a csv file

Column A Column B
ElementA 37
ElementB 40
ElementA 37
ElementB 40
ElementA 36
ElementB 40
ElementA 37
ElementB 38
ElementA 36
ElementB 40


I want to find the value in column B that occurs most frequently for the element in Column A and populate this value in a cube

So the cube should say

ElementA 37
ElementB 40

I want to avoid having a dimension that holds the numbers. Is there a simple way of doing this?

Maren

[Wim Gielis]

Whyt don't you do a CellPutN in the cube, against the specified column A element, but only IF the value (CellGetN) is strictly smaller than the new value coming in through the Data source ?

[MarenC]

Whyt don't you do a CellPutN in the cube, against the specified column A element, but only IF the value (CellGetN) is strictly smaller than the new value coming in through the Data source ?

Hi Wim,

Because then I would end up with the smallest value in the cube and not the one that occurs most?

Let us say 37 occurred 20 times and 36 occurred 18 times, I would want 37, but if 36 occurred 20 times and 37 occurred 18 times, I would want 36.

I.e. the one with the most occurrences.

Think of it like a Mode average, if I have 36,37,37,37,36,37,36 the mode would be 37

Maren

[Wim Gielis]

My bad, I interpreted the question differently and apparently wrongly.

I would suggest a cube with the additional dimension to count the number of occurrences. After that you can have a second TI process to update the data in the first cube.

[MarenC]

Hi Wim,

yes I wanted to avoid having the numbers in a dimension but probably have no choice.

Maren

[Wim Gielis]

In fact, if the source of the files is CSV, there is not much you can do about it. If it were data warehouse you could take some steps over there. For a text file you could look at Python or some dos commands, but never really as fast and efficient and simple as within TM1 itself. You could even clear the dimension if you’re worried about the number of elements.