Check Unique values on Column

[Motyl]

Hello
First, sorry for my english

I have two problems
1) How to prevent entry 0 by user in column in Cube? My idea is: add column "Error" and write error message, but maybe is better solution?
2) How to check that value in cell is unique entire column?
For example i have cube: Shops Priority

*........Priority.......Error
Shops
A01.....1
A02.....2
A03.....3.............Error
A04.....3.............Error

How to write rule?
It doesn't work:
['Priority bis ] = if (['Priority' ]=DB('Shops Priority', DIMNM('Shops', DIMIX('Shops', !Shops)+1 ), 'Priority'),
['Priority' ],
DB('Shops Priority', DIMNM('Shops', DIMIX('Shops', !Shops)+1 ), 'Priority bis' ));

['Error'] = S: If(['Priority bis']=['Priority'],'Error','')

[tomok]

It doesn't work:
['Priority bis ] = if (['Priority' ]=DB('Shops Priority', DIMNM('Shops', DIMIX('Shops', !Shops)+1 ), 'Priority'),
['Priority' ],
DB('Shops Priority', DIMNM('Shops', DIMIX('Shops', !Shops)+1 ), 'Priority bis' ));

['Error'] = S: If(['Priority bis']=['Priority'],'Error','')

No kidding it doesn't work. Do you think just comparing each element to the element right next to it will tell you if all the elements in a dimension are unique? Don't you have to compare each element to every other element? I just don't see how that could feasibly be done in a rule without blowing your whole system out of the water. For just a simple dimension with 10 elements would result in 90 possible comparisons that need to be done. Imagine how many that would be if you had several thousand elements, not to mention the number of combinations that would be added from additional dimensionality.

[Duncan P]

How many rows does this uniqueness check need to run over? If it is a small to reasonable number it is possible to have a special column that contains, as a string, a separator-delimited list of all the items chosen so far. Then the check can be performed with a simple SCAN. This will pick up and highlight the second and subsequent uses of any value. The string is constructed for a row by surrounding the choice for that row with delimiters and concatenating it to the string for the previous row. Of course the delimiter must be a character that cannot occur in any of the value choices.

[Motyl]

tomok, Yes, i know this is stupid rule, but i don't have idea how to do it (i'm new with tm1, i'm programmer for 7 years, but .Net and SQL)

Duncan P , not much <1000 rows
But with scan you cannot do it, because you will have something like this: 1;24;234;100;124;224 and scan for will return 2,5,6 (sorry for my english )

[Duncan P]

Make sure that the 'used_so_far' has the delimiter at both ends. Then put it at both ends of the string you are searching for.

Code: Select all

['Error'] = S: IF( 0 = SCAN( ';' | DB( 'thiscube', !dim1, !rows, 'entry' ) | ';', DB( 'thiscube', !dim1, DIMNM( 'rows', DIMIX( 'rows', !rows ) - 1 ), 'used_so_far' ) ), '', 'ERROR' );

[Motyl]

I do it with another way.
I built a process that implements "bubble sort" and automatically renumber values in cell.

[BariAbdul]

Hi Motyl,Would you be kind enough to post your solution please.Thanks

[Motyl]

Sorry, i can't because my company watches sources, but if i have later time, i write only algorithm.