Pes Posted March 1, 2005 Share Posted March 1, 2005 We need to calc the sum of the squares of the 4 highest values in 10 fields, all in one calculation. Any hints how to start ? (FM 6) TIA Link to comment Share on other sites More sharing options...
-Queue- Posted March 1, 2005 Share Posted March 1, 2005 Are they all in one record or one field in ten records? Link to comment Share on other sites More sharing options...
Pes Posted March 1, 2005 Author Share Posted March 1, 2005 Sorry, in one record.... Link to comment Share on other sites More sharing options...
-Queue- Posted March 2, 2005 Share Posted March 2, 2005 Create a calculation text field that concatenates the fields, Right( "00000" & field1, 6 ) & "¶" & Right( "00000" & field2, 6 ) & "¶" & Right( "00000" & field3, 6 ) & "¶" & ... & "¶" & Right( "00000" & field10, 6 ) The leading zeroes are to ensure that the numbers sort correctly. I used 5 in anticipation of the largest number being six digits. You can modify it if you expect larger numbers. Create a self-relationship based on the unique id for the record. Then create a value list based on the related calculation field. Create a final, unstored, calculation number field of RightWords( Substitute( ValueListItems( Status(CurrentFileName), "yourValueList" ), "¶", " " ), 1 ) ^ 2 + MiddleWords( Substitute( ValueListItems( Status(CurrentFileName), "yourValueList" ), "¶", " " ), WordCount(ValueListItems( Status(CurrentFileName), "yourValueList" )) - 1, 1 ) ^ 2 + MiddleWords( Substitute( ValueListItems( Status(CurrentFileName), "yourValueList" ), "¶", " " ), WordCount(ValueListItems( Status(CurrentFileName), "yourValueList" )) - 2, 1 ) ^ 2 + MiddleWords( Substitute( ValueListItems( Status(CurrentFileName), "yourValueList" ), "¶", " " ), WordCount(ValueListItems( Status(CurrentFileName), "yourValueList" )) - 3, 1 ) ^ 2 Since the value list will re-order the numbers in increasing order, the last four in the value list are the highest and are retrieved, squared, and summed. A screen refresh may be necessary to see the sum change when the numbers are modified. You can either navigate to another record or enter find mode, then enter browse mode, or something similar. Link to comment Share on other sites More sharing options...
Pes Posted March 2, 2005 Author Share Posted March 2, 2005 Thanks, meanwhile I found a solution myself. I first square the number, because I need that value elswhere also, and make the valuelist from a calc text field, then I can parse out the rank with PatternCount... This way I have the 4 values separately. Simple and easy... Thanks anyway... Link to comment Share on other sites More sharing options...
comment Posted March 2, 2005 Share Posted March 2, 2005 I hate doing this, but I have to ask: what happens if two or more of the 4 highest values are identical? Link to comment Share on other sites More sharing options...
Pes Posted March 2, 2005 Author Share Posted March 2, 2005 Good question... Even one step further, empty or zero...(the basic fields) Lucky for me this will never be in the actual application, but...yes it could be... With the PatternCount and Position function I can grab the highest value, next (can be equal) etc., no problem there. Problem will be with empty or zero.... Any idea ? Will also search...not that I could use it now...you never know... Link to comment Share on other sites More sharing options...
comment Posted March 2, 2005 Share Posted March 2, 2005 Best to use a script. Even better, break the values into separate records. Then you can sort descending, go to record 5, omit mutiple and do your calc. Link to comment Share on other sites More sharing options...
comment Posted March 2, 2005 Share Posted March 2, 2005 [ QUOTE ] With the PatternCount and Position function I can grab the highest value, next (can be equal) etc., no problem there. [/ QUOTE ] I don't quite follow. Link to comment Share on other sites More sharing options...
Pes Posted March 2, 2005 Author Share Posted March 2, 2005 Similar to: TextToNum(MiddleWords(ValueListItems("FileName";"Values"), PatternCount(ValueListItems("FileName";"Values") ; "¶");1)) returns the next highest value, ...same "¶")-1; 1)) the next etc.. To parse the rank it's similar, with Position(ValueListItems( "FileName";"Values");FieldName;1;1));"¶")+1, The usual stuff to parsing out a list item..... Link to comment Share on other sites More sharing options...
comment Posted March 2, 2005 Share Posted March 2, 2005 Ok, I see. But that's what I meant - let's say we have 5 values and we want the 2 highest. If the values are say, 10, 10, 5, 4, and 1, the value list will be: 1 4 5 10 So instead of 10, 10 you'll be getting 10, 5. Link to comment Share on other sites More sharing options...
-Queue- Posted March 2, 2005 Share Posted March 2, 2005 That's easily resolved. You can use a repeating index number field, where the first rep contains 1, the second 2, etc., and a repeating calc of Right( "00000" & Choose( TextToNum(Index) - 1, f1, Extend(f2), Extend(f3), Extend(f4), Extend(f5), Extend(f6), Extend(f7), Extend(f8), Extend(f9), Extend(f10) ) ^ 2, 6 ) & Middle( "ABCDEFGHIJ", Index, 1 ) Then, change the sum calc to TextToNum(RightWords( Substitute( ValueListItems( Status(CurrentFileName), "fAll" ), "¶", " " ), 1 )) + TextToNum(MiddleWords( Substitute( ValueListItems( Status(CurrentFileName), "fAll" ), "¶", " " ), WordCount(ValueListItems( Status(CurrentFileName), "fAll" )) - 1, 1 )) + TextToNum(MiddleWords( Substitute( ValueListItems( Status(CurrentFileName), "fAll" ), "¶", " " ), WordCount(ValueListItems( Status(CurrentFileName), "fAll" )) - 2, 1 )) + TextToNum(MiddleWords( Substitute( ValueListItems( Status(CurrentFileName), "fAll" ), "¶", " " ), WordCount(ValueListItems( Status(CurrentFileName), "fAll" )) - 3, 1 )) or your equivalent calc using PatternCount( ..., "¶" ) instead of WordCount. Link to comment Share on other sites More sharing options...
comment Posted March 2, 2005 Share Posted March 2, 2005 It's not working for me, because I can't get a value list from an unstored repeating field (as I surmised the value list should now be based). But I think I get the idea, and with only 10 fields it shouldn't be too hard to implement without a repeater. So basically you append a unique character to each value, make a value list from that, and in the final calc ignore the character, right? Link to comment Share on other sites More sharing options...
-Queue- Posted March 2, 2005 Share Posted March 2, 2005 You need a global index field, and then a constant relationship to lookup the global values into the index field when a new record is created. Then both fields can be indexed. Sorry for omitting that portion. Yes, you could do it without a repeating field. I just prefer the dynamics with it as opposed to hard-coding the value for each field. You can use a similar technique in pre-7 versions to force a value list to sort in its original order, which is quite useful when you want to, say, parse a path name into its individual folders, and then recombine the pieces. Using a repeating field for the value list, you can include the index field and sort by it. This doesn't work well in 7 because it insists on repeating the first repetition of the index instead. Link to comment Share on other sites More sharing options...
comment Posted March 2, 2005 Share Posted March 2, 2005 I am kicking myself real hard right now, because I have done the gIndex lookup countless times before, AND we have just recently talked about it no longer being necessary in 7. I guess I was in too much hurry to find out what the calc was about - couldn't decipher it by reading. I am still having reservations about using ValueListItems, due to refresh problems. Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts
Archived
This topic is now archived and is closed to further replies.