企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 500|回复: 7

Check Unique values on Column

[复制链接]

87

主题

373

帖子

560

积分

中级会员

Rank: 3Rank: 3

积分
560
QQ
发表于 2014-3-19 02:30:52 | 显示全部楼层 |阅读模式
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','')
回复

使用道具 举报

62

主题

411

帖子

546

积分

高级会员

Rank: 4

积分
546
QQ
发表于 2014-3-19 03:43:48 | 显示全部楼层
Motyl wrote: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.
回复 支持 反对

使用道具 举报

90

主题

419

帖子

604

积分

中级会员

Rank: 3Rank: 3

积分
604
QQ
发表于 2014-3-19 04:10:25 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

70

主题

437

帖子

578

积分

中级会员

Rank: 3Rank: 3

积分
578
QQ
发表于 2014-3-19 05:11:47 | 显示全部楼层
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   )

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

66

主题

363

帖子

509

积分

中级会员

Rank: 3Rank: 3

积分
509
QQ
发表于 2014-3-19 05:53:47 | 显示全部楼层
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: &#91;'Error'&#93; = S: IF( 0 = SCAN( ';' | DB( 'thiscube', !dim1, !rows, 'entry' ) | ';', DB( 'thiscube', !dim1, DIMNM( 'rows', DIMIX( 'rows', !rows ) - 1 ), 'used_so_far' ) ), '', 'ERROR' );

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

80

主题

407

帖子

587

积分

中级会员

Rank: 3Rank: 3

积分
587
QQ
发表于 2014-3-19 06:29:05 | 显示全部楼层
I do it with another way.
I built a process that implements "bubble sort" and automatically renumber values in cell.
回复 支持 反对

使用道具 举报

67

主题

407

帖子

559

积分

中级会员

Rank: 3Rank: 3

积分
559
QQ
发表于 2014-3-19 06:43:49 | 显示全部楼层
Hi Motyl,Would you be kind enough to post your solution please.Thanks
回复 支持 反对

使用道具 举报

80

主题

402

帖子

526

积分

中级会员

Rank: 3Rank: 3

积分
526
QQ
发表于 2014-3-19 06:55:02 | 显示全部楼层
Sorry, i can't because my company  watches sources, but if i have later time, i write only algorithm.
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2018-7-22 22:21 , Processed in 0.156476 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表