?
Solved

Get values from a list when positions are known (using formula)

Posted on 2003-03-13
17
Medium Priority
?
204 Views
Last Modified: 2013-12-18
I want to write a formula that returns the values from the list when positions are known. for eg.

List := A : B : C : D : E : F
Pos := 2 : 4 : 6

Any formulas that could return: B : D : F

I don;t think @Subset would help much here ..
0
Comment
Question by:navgup
[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
  • 7
  • 3
  • 2
  • +3
17 Comments
 
LVL 7

Expert Comment

by:scottrma
ID: 8130240
Why do you feel that @Subset would not work?
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 8130258
Double @Subsets will work.

First pass the position so that it will return upto the value for example @Subset(List ; Pos) will return A, B for the 2.

Now use the second @Subset on this with value as -1 like

@Subset(@Subset(List ; pos); -1)

This will return B for 2 , D for 4 and F for 6

-Arun.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 8130260
i got it i got it....

:)
0
Industry Leaders: 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!

 

Author Comment

by:navgup
ID: 8130390
I ve tried it before.
pos cannot be a list. That is what i found out.
If i use the function you stated, it will only return B, not others. In this case it returns only B

Am i missing something...
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 8130474
I have the formula for this.

List := "A" :"B" : "C" : "D" : "E" : "F";
Pos := 2 : 4 : 6;

digitList := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
idList := @text(@texttonumber(@Subset(digitList *+ @subset(digitList; -@elements(digitList)+1); @Elements(List))));
tmpList1 := @Unique(@Replace(List; List; idList));
tmpList2 := @Trim(@Replace(idList; @text(Pos); ""));
List1 := @Replace(tmpList2; idList; List);
@trim(@unique(@Replace(List; List1; "")));


If you appreciate it then increase points

~Hemanth
0
 

Author Comment

by:navgup
ID: 8130755
Thanks Hemanth, but I think there is something very minor wrong in the formula. It is returning all the values in the List.
Pl fix.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 8130831
If you copy and paste the formula then it should give you B;D;F

I have tested and posted it here.  Give me your formula here
0
 

Author Comment

by:navgup
ID: 8130995
Thanks Hemanth, but I think there is something very minor wrong in the formula. It is returning all the values in the List.
Pl fix.
0
 

Author Comment

by:navgup
ID: 8131045

List := allclasses_disp;
Pos := @Texttonumber(IAClassChoicesIDSel);

digitList := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
numList := @Text(@TextToNumber(digitList *+ digitList *+ digitList) + 1);
idList := @text(@texttonumber(numList; @Elements(List)));
tmpList1 := @Unique(@Replace(List; List; idList));
tmpList2 := @Trim(@Replace(idList; @text(Pos); ""));
List1 := @Replace(tmpList2; idList; List);
@trim(@unique(@Replace(List; List1; "")));


allclasses_disp is a computed text list field and could go upto 1000 values.
IAClassChoiceIDSel is editable text checkbox field with values selected as "2":"6":"8":"15":"99" ...

Now i want whatever value is selected from checkbox to return from allclasses_disp field.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 8131078
This calls for the underestimated @Keywords function.
Try this one.... it's tested in an agent!


List := @Explode("A B C D E F"; " ");
Pos := 2 : 4 : 6;

REM "Assume max. 99 entries.";
REM "Construct a list of 99 unique numbers";
Nr10 := @Explode("0 1 2 3 4 5 6 7 8 9"; " ");
Nr99 := @Subset(Nr10 *+ Nr10; -99);

REM "Resize Nr99 to match List\'s lengh";
REM "Prefix the remaining unique numbers to List";
REM "Each elements will then be labelled with its own number:";
REM "01A : 02B : 03C.... ";
NrList := @Subset(Nr99; @Elements(List)) + List;

REM "Now first convert Pos to similar, 2 digit numers in text";
Pos00 := @Left("0" + @Text(Pos); 2);

REM "Construct another list, prefixing Pos00 to each of the elements in List";
PosJoin := Pos00 *+ List;

REM "We now need all elements that occur in both lists";
ResultA := @Keywords(NrList; PosJoin);

REM "Remove all prefixed numbers";
ResultB := @Right(ResultA; 1);

@Prompt([OK]; "Result"; @Implode(ResultB; " : "))


List := @Explode("A B C D E F"; " ");
Pos := 2 : 4 : 6;

REM "Assume max. 99 entries.";
REM "Construct a list of 99 unique numbers";
Nr10 := @Explode("0 1 2 3 4 5 6 7 8 9"; " ");
Nr99 := @Subset(Nr10 *+ Nr10; -99);

REM "Resize Nr99 to match List\'s lengh";
REM "Prefix the remaining unique numbers to List";
REM "Each elements will then be labelled with its own number:";
REM "01A : 02B : 03C.... ";
NrList := @Subset(Nr99; @Elements(List)) + List;

REM "Now first convert Pos to similar, 2 digit numers in text";
Pos00 := @Left("0" + @Text(Pos); 2);

REM "Construct another list, prefixing Pos00 to each of the elements in List";
PosJoin := Pos00 *+ List;

REM "We now need all elements that occur in both lists";
ResultA := @Keywords(NrList; PosJoin);

REM "Remove all prefixed numbers";
ResultB := @Right(ResultA; 1);

@Prompt([OK]; "Result"; @Implode(ResultB; " : "))
0
 
LVL 13

Expert Comment

by:CRAK
ID: 8131091
Oops... I must have CTRL + V's twice!
use:
   List:=...
Up to
   @Prompt...
0
 

Author Comment

by:navgup
ID: 8131158
Hemanth, I think somewhere in the following line of your formula there is a problem. pl verify..

tmpList1 := @Unique(@Replace(List; List; idList));
it is still not working right.

Thanks
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8131955
Hello navgup,

Hemanth's formula works well. You have a typo.

Try this:

List := @Explode(allclasses_disp);
Pos := @TextToNumber(@Explode(IAClassChoicesIDSel));
digitList := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
numList := @Text(@TextToNumber(digitList *+ digitList *+ digitList) + 1);
idList := @text(@texttonumber(@Subset(digitList *+ @subset(digitList; -@elements(digitList)+1); @Elements(List))));
tmpList1 := @Unique(@Replace(List; List; idList));
tmpList2 := @Trim(@Replace(idList; @Text(Pos); ""));
List1 := @Replace(tmpList2; idList; List);
@Trim(@Unique(@Replace(List; List1; "")))

And please increase the points for Hemanth at least to 300.

It is really worth :-)

Cheers,
Zvonko

0
 

Author Comment

by:navgup
ID: 8139364
If i had that many points to spare i would have given it.

Thanks for the input.. i am still working on resolving the typo which i can't find. I ll give credit as soon as i m done.

0
 
LVL 63

Expert Comment

by:Zvonko
ID: 8139432
You do not see the typo?

Ok, Hemanth wrote:
idList := @text(@texttonumber(@Subset(digitList *+ @subset(digitList; -@elements(digitList)+1); @Elements(List))));


You wrote:
numList := @Text(@TextToNumber(digitList *+ digitList *+ digitList) + 1);
idList := @text(@texttonumber(numList; @Elements(List)));

Your any reason does this your line not rise a syntax error, I don't know why: (@texttonumber(numList; @Elements(List))

You see the difference?






0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 400 total points
ID: 8139485
Zvonko, thanks for filling that up for me. Been in a meeting for 6 hrs.

Navgup, I think I have written it in a manner that you can copy the bottom portion (which is transforming logic) and above line is input line (which I am sure that you will replace it with field values).

Here is the formula that you might want to try.

List := allclasses_disp;
Pos := @Texttonumber(IAClassChoicesIDSel);

digitList := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
idList := @text(@texttonumber(@Subset(digitList *+ @subset(digitList; -@elements(digitList)+1); @Elements(List))));
tmpList1 := @Unique(@Replace(List; List; idList));
tmpList2 := @Trim(@Replace(idList; @text(Pos); ""));
List1 := @Replace(tmpList2; idList; List);
@trim(@unique(@Replace(List; List1; "")));
0
 

Author Comment

by:navgup
ID: 8154316
Great piece of code. It rocks !
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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