Page 1 of 1
Find value with most occurences for a given element
Posted: Tue Feb 23, 2021 9:44 am
by 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
Re: Find value with most occurences for a given element
Posted: Tue Feb 23, 2021 12:16 pm
by 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 ?
Re: Find value with most occurences for a given element
Posted: Tue Feb 23, 2021 1:53 pm
by 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
Re: Find value with most occurences for a given element
Posted: Tue Feb 23, 2021 2:05 pm
by 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.
Re: Find value with most occurences for a given element
Posted: Tue Feb 23, 2021 2:46 pm
by MarenC
Hi Wim,
yes I wanted to avoid having the numbers in a dimension but probably have no choice.
Maren
Re: Find value with most occurences for a given element
Posted: Tue Feb 23, 2021 3:03 pm
by 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.