Niet32 Posted January 26, 2006 Posted January 26, 2006 This may or may not be the right way to do it so I am open to any suggestions: I have 10 markets with store locations in each market. I have different number of storesfor each market. I need to rank the zip codes within each market and would like to automate the process. The main problem is having the script recognize when the next market begins. Any help would be appreciated.
Ender Posted January 26, 2006 Posted January 26, 2006 You haven't said much about your structure, but if we make some assumptions, this can be done: If you have a Store table, with a Market field and a Zip Code field, you can add a couple fields to do determine the counts for each Zip Code. Add these fields: Count of Records (summary) = Count of Record ID Count by ZipCode (calculation, number result) = getsummary ( Count of Records ; Zip Code ) When the records are sorted by Market and Zip Code, with the sort option on Zip Code set to reorder based on the Count of Records summary field, the records will be sorted by Market, then by the Zip Code count. If desired, a columnar report with sub-summary parts can be used to display the gouped counts. See attached example. If an actual rank number is needed, there are a couple techniques that can be used. Let us know. MarketRanks.fp7.zip
Niet32 Posted January 30, 2006 Author Posted January 30, 2006 Thanks for the detailed info! Yes I do need to have actual rankings. The main problem with writing a script is every market could have a different number of Zips. So how do you set up a script so that it ranks 10 Zips in one market and 17 in another? Agains thanks for the detailed answer.
Ender Posted January 30, 2006 Posted January 30, 2006 One way is to use a script to loop through the records, and at each change in Zip, increment a rank counter (a global) and set the Rank field. Then at each change in Market, reset the rank counter back to 1 and continue. Another method scans a value lists made up of the zip codes to determine the rank. Suppose you have a conditional value list "Zipcodes" that shows all the zipcodes for the current market, then a calc like this will show the rank of each store within that market: Rank (calculation, number result) = Let( values = ValueListItems ( Get ( FileName ); "Zipcodes"); PatternCount ( Left ( values; Position ( values; Zip Code; 1; 1 ) ); ¶ ) + 1 ) I'm not sure which method would perform better.
Tombstone0 Posted April 28, 2006 Posted April 28, 2006 The only problem with this custom function is that if there's a tie it will not skip the subsequent ranking. it will provide the ranking [color:red]1, 2, 2, 3 rather than [color:red]1, 2, 2, 4 To correct this issue I changed my number I was ranking (in the value list) from [color:red]average to [color:red]average & "-" & serial and I changed the custom function to the following: [color:red]Let( list = ValueListItems ( Get(FileName) ; ValueListName) ; PatternCount( Left("¶" & list & "¶"; Position("¶" & list & "¶";"¶" & Field & "-";1;1)); "¶") ) Basically I changed [color:red]"¶" & Field & "¶" to [color:red]"¶" & Field & "-"
Recommended Posts
This topic is 6786 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now