Find value with most occurences for a given element

Post Reply
MarenC
Posts: 140
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Find value with most occurences for a given element

Post by MarenC » Tue Feb 23, 2021 9:44 am

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
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Find value with most occurences for a given element

Post by Wim Gielis » Tue Feb 23, 2021 12:16 pm

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 ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

MarenC
Posts: 140
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Find value with most occurences for a given element

Post by MarenC » Tue Feb 23, 2021 1:53 pm

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
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Find value with most occurences for a given element

Post by Wim Gielis » Tue Feb 23, 2021 2:05 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

MarenC
Posts: 140
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Find value with most occurences for a given element

Post by MarenC » Tue Feb 23, 2021 2:46 pm

Hi Wim,

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

Maren

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Find value with most occurences for a given element

Post by Wim Gielis » Tue Feb 23, 2021 3:03 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Post Reply