5 Replies Latest reply: Apr 27, 2011 9:19 AM by KOENIG Yvan
Luiz Siqueira Neto Level 1 Level 1 (0 points)

I have a table with bank transactions, the third cell have "Memo", some kind of description, I want to classify the transaction based on words found in the "Memo" based in another table with first column with classification and the second column with words (can be more than one word) to find.

 

 

Example:

Table Transaction:

 

ValueBalanceMemoCategory
-100,001900,00Company Phone - electronic bill Communication
-200,001700,00School paymentEducation
1000,002700,00Deposit trough machine?
-20,002680,00Bob's DinerFood
-40,002640,00Star Gas StationFuel

 

Table Category:

 

CategoryKey
CommunicationPhone, Cell
EducationSchool
FuelGas, Ethanol
FoodDiner, Restaurant

 

 

So far I found 3 possible solutions but I'm looking for a elegant and easy way to do it, it need be easy to manage and add words as criterions.

 

Found Solutions:

 

1 - nested "IF" (Complicated to manager and to add new keys for new categories)

2 - Add columns to the first table, one column for each key to test, then use "Lookup" to get Category in the column header (Not elegant)

3 - Trough AppleScript (very slow).

 

I make a research to find a way to make some like a "Loop" or a "Repeat until" with only Numbers formulas but I don't found nothing.

Any help will be appreciated.

 

Thanks.

 

Ps: this is my AppleScript (my real table is a little different than that in this message, my table have a subcategory too).

 

---------------

 

on know_category from array to memo

          set found to {"?", "?"}

          repeat with description in array

                    repeat with |key| in item 3 of description

                              if |key| is in memo then

                                        set found to description

                                        exit repeat

                              end if

                    end repeat

                    if not found is {"?", "?"} then

                              exit repeat

                    end if

          end repeat

          return found

end know_category

 

on categories_as_array from table

          set |result| to {}

          tell application "Numbers"

                    repeat with |row| in rows of |table|

                              set values to value of cells of |row|

                              set AppleScript's text item delimiters to ", "

                              set Category to first item of values

                              set subcategory to second item of values

                              set keys to text items of (third item of values)

                              set AppleScript's text item delimiters to " "

                              set end of |result| to {Category, subcategory, keys}

                    end repeat

          end tell

          return |result|

end categories_as_array

 

tell application "Numbers"

          set categories to categories_as_array of me from table "Category" of sheet "Config" of first document

 

          set names to {}

          repeat with |sheet| in sheets of first document

                    set end of names to name of |sheet|

          end repeat

 

          set |name| to {choose from list names} as text

 

          repeat with |row| in rows of table "Transactions" of sheet |name| of first document

                    if not (value of item 7 of cells of |row|) is "Category" then

                              set memo to value of item 5 of cells of |row|

                              set found to know_category of me from categories to memo

                              set value of item 7 of cells of |row| to first item of found

                              set value of item 8 of cells of |row| to second item of found

                    end if

          end repeat

end tell

  • Level 8 Level 8 (41,780 points)

    I apologizes but your handler categories_as_array seems to be wrong.

     

    When I run the script it fails because it doesn't find a third item in values

     

    I edited it this way :

     

    on categories_as_array from table

              set |result| to {}

              tell application "Numbers"

                        repeat with |row| in rows of |table|

                                  set values to value of cells of |row|

                                  set AppleScript's text item delimiters to ", "

                                  set Category to first item of values

                                  set subcategory to second item of values

                                  try

                                            set keys to text items of (third item of values)

                                  on error

                                            set keys to ""

                                  end try

                                  set AppleScript's text item delimiters to " "

                                  set end of |result| to {Category, subcategory, keys}

                        end repeat

              end tell

              return |result|

    end categories_as_array

     

    but the returned list which is :

    {{"Category", "Key", ""}, {"Communication", "Phone, Cell", ""}, {"Education", "School", ""}, {"Fuel", "Gas, Ethanol", ""}, {"Food", "Diner, Restaurant", ""}}

    is not the expected one.

     

    My proposal would be :

     

    on categories_as_array from table

              set |result| to {}

              tell application "Numbers"

                        repeat with |row| in rows of |table|

                                  set {Category, values} to value of cells of |row|

                                  set AppleScript's text item delimiters to ", "

                                  set subcategory to first text item of values

                                  try

                                            set keys to text items 2 thru -1 of values

                                  on error

                                            set keys to {}

                                  end try

                                  set AppleScript's text item delimiters to " "

                                  set end of |result| to {Category, subcategory, keys}

                        end repeat

              end tell

              return |result|

    end categories_as_array

     

    which returns :

     

    {{"Category", "Key", {}}, {"Communication", "Phone", {"Cell"}}, {"Education", "School", {}}, {"Fuel", "Gas", {"Ethanol"}}, {"Food", "Diner", {"Restaurant"}}, {"fake", "sub1", {"key1", "key2", "key3"}}}

     

    As you see, for tests I added a row to your table.

     

    It contains :

    fake

    sub1, key1, key2, key3

     

    Yvan KOENIG (VALLAURIS, France) mercredi 27 avril 2011 11:38:31

  • Luiz Siqueira Neto Level 1 Level 1 (0 points)

    Thank you about your help, with your script version my simplified table version (as example)

    now works fine. Like I said before my real table is a little different and my script works there (but slow).

     

    I'm trying to create a solution that works without AppleScript and easy to add and remove "Category", a nested "If" isn't a good solution because easily becomes large and complex.

     

    Again, thanks, all help will be appreciated.

     

    For avoid confusion I embedded the real table model here:

    (PS: Some texts are in Brazilian Portuguese)

     

     

    <Edited by Host>

  • Level 8 Level 8 (41,780 points)

    Now that I got your true table I understand.

     

    In the one which you posted, there are only two columns : Categories and Keys but your handler requires three columns as those of the true table to behave flawlessly.

    I guess that you grabbed the tip setting directly the values in a list :

    set {Categories, Subcategory, keys} to value of cells of |row|

     

    I heave a question to ask :

     

    Where did you learn the syntax

    on know_category from array to memo

    which I discovered in your script ?

     

    I am accustomed to

    on know_category (array, memo)

     

    Yvan KOENIG (VALLAURIS, France) mercredi 27 avril 2011 17:01:00

  • Luiz Siqueira Neto Level 1 Level 1 (0 points)

    You can find this syntax (and many more) in Apple Script Language Guide (Chapter 5, starting in page 69)

     

    I particularly love this kind of syntax, it looks more human, any one can read it. Normally I avoid the use of "_" using instead "|" like this example: |categories as array| instead categories_as_array; I try to create valid English sentences.

  • Level 8 Level 8 (41,780 points)

    Thaks

     

    I missed it.

     

    I used the vertical bar but when I switched from Tiger to Leopard, all my scripts using this character were broken.

    The bar was gone so I no longer used it.

     

    Yvan KOENIG (VALLAURIS, France) mercredi 27 avril 2011 18:19:20