Solved

formula for a view

Posted on 2004-04-07
32
491 Views
Last Modified: 2013-12-18
I've a form named "Cus" in which one of the field has the following properties.

fieldname = MTNO
Type = text,
allow multiple values is checked.

in some of the forms this field has duplicate values in it.

I want to create a view which will show only records which has duplicate values in this field.

Please advise on what would the SELECT statement be in the view.

thanks
Kalios

0
Comment
Question by:kalios
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 7
  • +3
32 Comments
 
LVL 13

Expert Comment

by:CRAK
ID: 10774040
Select @Elements(MTNO) != @Elements(@Trim(MTNO))
0
 
LVL 13

Assisted Solution

by:CRAK
CRAK earned 20 total points
ID: 10774045
Note that if the field should contain empty entries, those documents would show to!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10774062
CRAK! Pssst! It's not @Trim, it's @Unique...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 63

Assisted Solution

by:Zvonko
Zvonko earned 40 total points
ID: 10774077
Here is your SELECT formula:

SELECT (FORM = "Cus" & (@Implode(MTNO) != @Implode(@Unique(MTNO))))

0
 
LVL 2

Author Comment

by:kalios
ID: 10774443
BEAUTIFUL , I used Zvonko's, It works like a charm. Can I pick the duplicate values into a variable and show it up in one of the column in this view.

thanks
Kalios
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10774528
Actually, CRAK's corrected formula is faster (Implode is slow):

    SELECT Form="Cus" & @Elements(MTNO)<>@Elements(@Unique(MTNO))

Duplicates can be shown in a column, see http://www.experts-exchange.com/Q_20789842.html
   
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10774562
Yeap!

Add a Column with this formula:

@ReplaceSubstring(@Implode(MTNO;";");@Unique(MTNO)+";";"")


0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10774635
Won't work.
Check that
    @ReplaceSubstring("ABC;CDE;ABC;CDE"; "ABC;":"CDE;"; "")
will result in
    "CDE"
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10774862
Take this:

@Unique(@Explode(@ReplaceSubstring(@Implode(@Unique(MTNO)+";"+MTNO;";");@Unique(MTNO)+";"+@Unique(MTNO);";")))


0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10774931
Slightly shorter:

@Explode(@Unique(@Trim(@Replace(@Unique(MTNO)+";"+MTNO;@Unique(MTNO)+";"+@Unique(MTNO);";"))))


Zvonko



0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10774953
Here slighty better readable:


@Trim(@Explode(@Unique(@Replace(@Unique(MTNO)+";"+MTNO;@Unique(MTNO)+";"+@Unique(MTNO);";"))))



0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10775151
Is this an advantage:


$UV:=@Unique(MTNO);
@Trim(@Explode(@Unique(@Replace($UV+";"+MTNO;$UV+";"+$UV;";"))))



0
 
LVL 2

Author Comment

by:kalios
ID: 10775229
Zvonko, I used this,

@Trim(@Explode(@Unique(@Replace(@Unique(MTNO)+";"+MTNO;@Unique(MTNO)+";"+@Unique(MTNO);";"))))

this is eliminating duplicates and showing only values from the field that are not duplicates.
I want to see only duplicates.

0
 
LVL 13

Expert Comment

by:CRAK
ID: 10775279
Wow....
Such a simple question and such a load of comments.
Good point Sjef: @Unique instead of @Trim! We should have some kind of a remote desktop installed. I could use a "pssst" like that every now and then!
;-))
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10775357
My tests yield only duplicate values.
What were your values?
Are you sure that your values are separated to multiple values? Look in Document item properties.

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10775368
I cannot recollect who it was with the duplicate values in a multiple value variable. Blast! There was a post about how to obtain only the duplicates from a list of values. Can't think of the wasy to do that anymore. Aaaaargh! Alzhomer again...
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10775387
You mean it was shorter than my @formula?!?  :-)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10775457
No, eh, not shorter but, eh, better...

Sjef :)
0
 
LVL 2

Author Comment

by:kalios
ID: 10775726
The values I've in the MTNO field in one of the record are as follows:

1172235198   ; 1142313824   ; 1124166688   ; 1171098812   ; 0605377812   ; 0671364626   ; 0699190916   ; 2796283992   ; 2701033039   ; 2796284565   ; 2649401222   ; 2628401223   ; 2649401222   ; 2628401223   ; 0203817672   ; 3588207244   ; 0202817776   ; 3378139277   ; 2371186309   ; 2376268516   ; 2399243455   ; 3355167738   ; 2317176392   ; 2265003840   ; 2314075706   ; 2324075709   ; 2344075704   ; 2265003158   ; 3347085949   ; 3347086546   ; 3359127978   ; 3357167700   ; 2890473651   ; 1004722834   ; 1018180338  

There are 35 values in the MTNO field above.

In the above values 2628401223 is duplicate. It is there 2 times.
Now I only want to display this value in the new column.  But instead it is displaying all.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10775753
Got it again!

n:= @Explode("0 1 2 3 4 5 6 7 8 9"; " ");
nn:= @Subset(n *+ n; @Elements(MNTO));

@Replace(@Trim(@Replace(nn; @Replace(@Unique(MNTO); MNTO; nn); "")); nn; MNTO)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10775767
Sorry, I checked (with MY formula ;), there are two duplicates:
2649401222  and 2628401223  
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 65 total points
ID: 10775786
If you have many values, my formula will be a problem, most certainly in a view. If necessary, i.e. when there are more than 99 documents, but less than 1000, replace
   nn:= @Subset(n *+ n; @Elements(MNTO));
with
   nn:= @Subset(n *+ n *+n; @Elements(MNTO));
0
 
LVL 13

Expert Comment

by:CRAK
ID: 10778254
> Sorry, I checked (with MY formula ;), .....

You sound like a salesman Sjef!
<LOL>

Come on Zvo! It's <|;-) 's turn!
0
 
LVL 4

Expert Comment

by:sreeser
ID: 10779034
Now I rememeber why I avoid formulas like the plague!!! LOL
0
 
LVL 2

Author Comment

by:kalios
ID: 10779518
Splendid. I used this and it works.....
n:= @Explode("0 1 2 3 4 5 6 7 8 9"; " ");
   nn:= @Subset(n *+ n *+n; @Elements(MNTO));

@Replace(@Trim(@Replace(nn; @Replace(@Unique(MNTO); MNTO; nn); "")); nn; MNTO)

thanks
Kalios
0
 
LVL 13

Expert Comment

by:CRAK
ID: 10781536
sreeser,
Don't you agree that formula's like this demonstrate the power if @functions?
Especially list operations... @Exlpode, @Implode, @Word, @Unique, @Trim.... you always need to write (or load) additional functions (with temporary variables, loops etc.) in LS to archieve the same!

How would you solve this then? Read the values, loop through the resulting array and.... store duplicates in a hidden field, which can then be showed in the view? Yak! ;-))
I have a feeling that kalios is better off with the current solution!

Of course, LS also has great advantages, but @functions are often underestimated! This was a demonstration of true art!
<LOL>
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10781632
CRAK,

> This was a demonstration of true art!

Tears come in my eyes... It's too much

Sjef :D

Kalios, these formulae tend to make a view  s l o w , for the first two lines are evaluated for every document again. I suppose you only have a few documents matching the original condition, and that the view has a temporary nature, so it won't matter much. Normally, it's better to put long formulae in a computed-field, saves a lot of time in the view.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 10781763
A ha.... this is where sreeser is going to post the ultimate solution!
;-))
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 10786090
Zvo Bro !!!

you are here ?  Wow... I was just mentioning in one of my questions that I am missing you all...  Come join my question. We can have our usual casual chat.

:-)
Arun.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10786203
Hi Bro!

Which Q do you mean?
http:Q_20933940.html
http:Q_20910237.html

I will travel next morning to Eastern Holidays and will be back on Tuesday.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 10786331
The 1st one Zvo... the -40.html
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10786346
Ah, ok, thanks. See you there.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question