Latest Contributions by XanderBakker

Hi Don Sjoboen , Then that's where I think the error refers to.  I assume it will not any information on the fields that are not included in the Distinct fields. Can you return the customers just after the Distinct to verify? ... View more

Hi Don Sjoboen , I assume that when you create the result after distinct with 1 field, you only try to retrieve the ROUTE field and not the other fields, right? The fact that it shows the "customer distinct count: 3" implicates that the Distinct was executed correctly. A console statement that should be executed after an error occurs does not get executed.  I agree that there is room for improvement when debugging an expression, but using these console statements help to determine where an error occurs.  ... View more

Hi Jared Pilbeam , That is correct I did a field calculation on a new field I created. You can also use the expression in the symbology profile or use it in the pop-up if you can't change the schema of the data. ... View more

Hi Don Sjoboen , So, what I deduce from your answer is that the fields are numeric, and yet it works when you create a query as if the field is a string. I think I have seen this go wrong in the past and I would recommend against it, since it is not guaranteed that it will work in the future. I think it might even be possible to avoid this by using the query as: var sql = "CONNECTION_OBJECT = @id";‍ ... View more

Hi Don Sjoboen , Did you try changing the "sql" like this? var tbl = FeatureSetByPortalItem(Portal('https://organization.com/'), 'ItemID', 1); Console(Count(tbl)); var id = $feature["CO_ID"]; Console(id); var sql = "CONNECTION_OBJECT = " + id; Console(sql); var customers = Filter(tbl, sql); var myArray = customers; var cnt = Count(customers); Console(cnt); var result = ""; if (cnt > 0) { for (var k in myArray) { /* var ROUTE = myArray[0]; console(ROUTE); result += TextFormatting.NewLine + "Routes: " + ROUTE +*/ result += TextFormatting.NewLine + k; } } else { result = "No customer data" } Console(result); return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ ... View more

Hi jpilbeam , I also notice that some of the keywords like "Textiles / Fabrics / Drapes / Curtains" are not found in the final groups. Is that correct? Should the words "Textiles", "Fabrics", etc be evaluated separately? ... View more

Hi [email protected] , I would indeed be nice and I voted your idea up... ... View more

HiDon Sjoboen , Quick question; is the field CONNECTION_OBJECT numeric or string? If it is numeric you should omit the quotes around the ID when you construct the SQL. ... View more

Hi Jared Pilbeam , Edit 1: I just noticed an error in the code, and I am in the process of correcting it... Edit 2: Found the error and corrected it (not add null values to groups list) For an Arcade solution I suggest you work with an object (or dictionary). See the code below: function GetGroup(keyword) { var dct_groups = {'Appliance Recycling': 'Air Conditioner;Appliance;Refrigerator;Dishwasher;Dryer;Oven;Stove;Washer;Water Heater', 'Asbestos Info / Removal Service': 'Asbestos', 'Automobile & Boat Reuse Recycling': 'Airplane; Automobile;Boat;Car;Recreation Vehicle;RV;Truck', 'Awards & Recognition': 'Awards (Eco-Award)', 'Batteries': 'Lead-Acid Batteries;Single Use AA, AAA, C, D, 9-Volt;Batteries - Rechargeable, BackUp;Lead-Acid Batteries;Rechargebale;Disposable Batteries;Rechargeable;Automotive Batteries;Boat Batteries;Lithium Batteries;Button Batteries', 'Bicycle Reuse Donation': 'Bicycles', 'Book Recycling': 'Books - Hardcover Recycling;Books - Softcover Recycling', 'Cellular Telephone Reuse & Recycling': 'Cell Phone - Retail Drop-Off;Cell Phone - Electronic Drop Off', 'Clean - Up Services': 'Clean-Up of Properties;', 'Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations': 'Clothing;Gym Shoes / Sneakers / HighTops;Linens / Blankets / Tableclothes / Towels;Textile / Fabrics / Drapes / Curtains;Shoes / Boots;Purse or Handbag', 'Commercial Hazardous Waste': 'Hazardous Waste Services;Chemical Business Waste', 'Composting Facilities/Landscape Waste transfer Stations': 'Composting Facilities/Landscape Waste transfer Stations;Landscape Material Recycling', 'Computer Reuse & Recycling': 'Business Electronic Devices', 'Construction & Demolition Recycling': 'Asphalt;Brick;Building Construction & Demolition Debris;Carpet;Carpet Padding;Concrete;Dirt;Drywall;Gravel;Padding;Roofing Materials;Shingles;Soil;Stone;Vinyl Siding;Windows;Wood-Untreated;Pallets', 'Cooking Oil/Grease Recycling & Trap Service': 'Cooking Oil;Grease Recycling;Grease Trap Services', 'Crayon Recycling': 'Crayons', 'Curbside Recycling': 'Aerosol Cans (empty) Curbside;Aluminum Can Curbside;Aluminum Foil Curbside;Cardboard & Chipboard Curbside;Curbside Recycling;Glass Bottles & Jars Curbside;Juice Boxes/Drink Pouches Curbside;Paper (magazines, phone bks, etc) Curbside;Plastic #1 (PET) & #2 (HDPE) Containers;Plastic #3, #4, #5, #7, Containers Curbside;Six-Pack Rings Curbside;Tin / Steel Cans (soup,cofffee, etc) Curbside', 'Demolition Material Recycling & Disposal': 'Construction Material Reuse;Demolition Material Reuse / Recycling', 'Disposable Batteries': 'Batteries - Single Use AA, AAA, C, D, 9-Volt', 'Donation': 'Books - Hardcover;Computer Reuse/Donation;Couch;Sofa;Toys', 'Drum & Cylinder Disposal or Recycling': 'Cylinders;Drums', 'Dry Cleaners (Green)': 'Dry Cleaning', 'Electronics': 'Adding Machines;Answering Machines;Calculators;Cameras;Cassettes;CB\'s/Two-way radios;CD Players / Laser Disc Players;CD ROM Drives;CDs, DVDs;Cell Phone - Electronic Drop-Off;Computer Recycling;Computer Recycling Drop-Off;Copy Machine;Cords & Cables;Digital Clocks;DVD Machine;Electronic Mice;Electronics;Fax Machines;Floppy Disks;Hand Held Games;Hard Drives;Joysticks/Game controls;Keyboards;Microwaves;Modems;Monitors;Pagers;Palm Organizers;Paper shredders;Portable Radio; Postage Machines;Printers;Scanner Machines;Speakers/Stereo Systems;String Lights / Holiday String Lights;Sump Pump;Tape Drives;Telephone;Televisions;Thermometers (digital);Typewriters/Word Processors;UPS Battery Backups;VCR Machine;VHS Tapes;Video Game Players; Zip Drives', 'Eye Glass Reuse': 'Eye Glasses', 'Fire Extinguisher Refilling or Recycling': 'Fire Extinguishers', 'Fluorescent Lights': 'CFLs;Compact Fluorescent Light Bulbs;Fluorescent Lights', 'Food Donation': 'Food Donation', 'Furniture & Office Equipment ': 'Book Cases;Cubical Walls;Desks;File Cabinets;Furniture & Office Equipment;Office Chairs', 'Geothermal Energy': 'Geothermal', 'Gift Cards': 'Gift Cards (merchant plastic money card)', 'Green Energy Supplier': 'Energy Supplier', 'Grocery Bag': 'Grocery Bags', 'Heating Oil Tank Removal': 'Heating Oil Tank Removal', 'Household Hazardous Waste (HHW)': 'Aerosol Products;Antifreeze;Automotive Batteries;Automotive Fluid;Boat Batteries;Button Batteries;Cleaning Chemicals;Compact Fluorescent Light Bulbs - HHW Drop-Off;Drain Cleaners;Driveway Sealer;Fertilizers;Fluorescent Light Bulbs;Gasoline-Oil Mix;Hazardous Household Materials;HHW;Household Chemicals;Lawn & garden chemicals;Lithium Batteries;Medication (old or unwanted);Mercury / Mercury Products;Motor Oil;Nail Polish Remover;Oil Filters;Oil-based Paint / Stain / Varnish;Old Gasoline;Paint Thinners;Pesticides;Pool Chemicals;Rechargeable Batteries;Solvents;Thermometers (glass);Used Oil;Medication (old or unwanted);Thermometers (glass);Cough Medicine;Inhalers;Medicated Shampoo;Ointment / Medicated Ointment;Over-the-Counter Medication;Pills, Pharmaceuticals, Medication;Prescription Medication;Sunscreen;Vitamins', 'Landfill & Transfer Stations': 'Landfills & Transfer Stations;Beverage Carrier Straps;Six-Pack Rings;Plastic #1, PET;Plastic #2, HDPE;Plastic #3, PVC;Plastic #4, LDPE;Plastic #5, PP;Plastic #7, Other Container;Aerosol Cans (empty) Drop-Off;Aluminum Can Recycling Drop-Off ;Aluminum Foil Recycling Drop-Off;Glass Bottles and Jars Drop-Off;Juice Boxes / Drink Pouches Drop-Off;Tin / Steel Cans (soup, coffee, etc) Drop-Off;', 'Latex Paint': 'Latex Paint', 'Lead-base Paint Concerns': 'Lead Paint', 'Manure': 'Manure', 'Matress Recycling': 'Matress & Box Springs', 'Medical Equipment (durable)': 'Canes;Crutches;Portable Toliets;Shower Chairs;Wheelchairs', 'Medication Drop-Off': 'Cough Medicine;Inhalers;Medicated Shampoo;Ointment / Medicated Ointment;Over-the-Counter Medication;Pills, Pharmaceuticals,Medication;Prescription Medication;Sunscreen;Vitamins', 'Metal Recycling': 'Scrap Metal;Aluminum / Aluminum Can Scrap;Brass;Copper;Iron;Steel Scrap;', 'Motor Oil - Bulk': 'Motor Oil in Buk Drums', 'Motor Oil Recycling (DIY)': 'Motor Oil - DIY', 'Packaging Materials': 'Packing Peanut ;Styrofoam Peanuts;Cardboard Boxes', 'Pallet Reuse or Recycling': 'Pallets', 'Paper Recycling': 'Cardboard;Chipboard;Colored Paper;Construction Paper;Junk Mail;Magazines;Newspaper;Office Paper;Paper Recycling;Shredding Services;Clored Paper', 'Plastic Carrier Strap Recycling': 'Beverage Carrier Straps;Six-Pack Rings', 'Plastic Container Recycling': 'Plastic #1, PET;Plastic #2, HDPE;Plastic #3 PVC;Plastic #4, LDPE;Plastic #5, PP;Plastic #7, Other Container', 'Pop-Tab Recycling': 'Pop-Tab', 'Printer/Toner Cartridge & Ribbon Recycling': 'Inkjets (Ribbons);Printer/Toner Cartridges', 'Propane Tank Disposal or Recycling': 'Propane Tanks', 'Rechargeable Battery': 'Batteries - Rechargeable, BackUp, Lead-Acid;Rechargeable', 'Recycling and Waste Collection Companies': 'Recycling Collection Company;Garbage Collection Company;Waste Collection Company', 'Repair Services': 'Sharpening Services', 'Reuse & Resale Shops': 'Reuse/Resale', 'Smoke Detectors ': 'Smoke Detectors ', 'Solar Energy': 'Solar Energy', 'Styrofoam or Polystyrene Recycling': 'Plastic #6, PS, Polystyrene;Styrofoam', 'Tire Recycling': 'Tire', 'Water Conservation': 'Rain Barrels;Rain Garden', 'Wind Energy': 'Small Wind'} for (var group in dct_groups) { var keywords = dct_groups[group]; if (Find(keyword, keywords)>-1) { return group; } } return null; } //var test = "Automobile and Boat Reuse Recycling;Batteries;"; var test = "Bicycles;Clothing;Gym Shoes / Sneakers / HighTops;Linens / Blankets / Tableclothes / Towels;Purse or Handbag;Shoes / Boots;Textiles / Fabrics / Drapes / Curtains;Books - Hardcover;Couch;Sofa;Toys;Book Cases;Desks;File Cabinets;"; var lst = Split(test, ";"); var groups = []; for (var i in lst) { Console(i) var kw = lst[i]; if (!IsEmpty(kw)) { Console(" - kw:" + kw); var group = GetGroup(kw); if (!IsEmpty(group)) { Console(" - group:" + group); groups[Count(groups)] = group; } } else { Console(" - item is empty"); } } Console("groups:" + groups); if (Count(groups) > 0) { var distinct_groups = Distinct(groups); Console("distinct_groups:" + distinct_groups); } var dct_groups = {}; for (var i in groups) { var group = groups[i]; if (HasKey(dct_groups, group)) { dct_groups[group] += 1; } else { dct_groups[group] = 1; } } Console("dct_groups:" + dct_groups); var cntmax = 0; var finalgroup = ""; for (var group in dct_groups) { var cnt = dct_groups[group]; if (cnt > cntmax) { finalgroup = group; cntmax = cnt; } } Console("finalgroup: " + finalgroup); return finalgroup;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ With a random test value of (see line 79): "Bicycles;Clothing;Gym Shoes / Sneakers / HighTops;Linens / Blankets / Tableclothes / Towels;Purse or Handbag;Shoes / Boots;Textiles / Fabrics / Drapes / Curtains;Books - Hardcover;Couch;Sofa;Toys;Book Cases;Desks;File Cabinets;" It writes to the Console: 0 - kw:Bicycles - group:Bicycle Reuse Donation 1 - kw:Clothing - group:Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations 2 - kw:Gym Shoes / Sneakers / HighTops - group:Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations 3 - kw:Linens / Blankets / Tableclothes / Towels 4 - kw:Purse or Handbag - group:Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations 5 - kw:Shoes / Boots - group:Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations 6 - kw:Textiles / Fabrics / Drapes / Curtains 7 - kw:Books - Hardcover - group:Book Recycling 8 - kw:Couch - group:Donation 9 - kw:Sofa - group:Donation 10 - kw:Toys - group:Donation 11 - kw:Book Cases - group:Furniture & Office Equipment 12 - kw:Desks - group:Furniture & Office Equipment 13 - kw:File Cabinets - group:Furniture & Office Equipment 14 - item is empty groups:["Bicycle Reuse Donation","Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations","Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations","Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations","Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations","Book Recycling","Donation","Donation","Donation","Furniture & Office Equipment ","Furniture & Office Equipment ","Furniture & Office Equipment "] distinct_groups:["Bicycle Reuse Donation","Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations","Book Recycling","Donation","Furniture & Office Equipment "] dct_groups:{"Bicycle Reuse Donation":1,"Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations":4,"Book Recycling":1,"Donation":3,"Furniture & Office Equipment ":3} finalgroup: Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ And now we see something interesting. There are multiple groups returned and since the one that occurs most is "Clothing, Lines...." this is the one that is assigned. However, this poses a question. Should this occur? ... View more

Hi Jared Pilbeam , I had not noticed the finalgroups file in the ZIP. Sorry about that. I had a look at it and since I felt like Arcade I first created something with that. Let me post that in the other questions so that it makes a little more sense for other that read the thread. After that I will post back here the python code.  ... View more

Hi [email protected]_tacoma , A quick question: is you field "CONNECTION_OBJECT" string o numeric? If numeric, you should change the sql you use in the Filter (take out the quotes). ... View more

Hi Jared Pilbeam , I just had a look at the data you shared and I think there is still some more information required to create the expression or Python script to group the values together. At this moment I am looking at 65 unique categories, 265 unique keywords (when I split the values in the field on the ";" character) and there 192 unique combinations between the two fields. So the main question is; what are the groups that you want to obtain and what defines if an element belongs to a group?   If I look at the example you shared before: The first group "Appliances" contains this word in both the keywords as in the categories fields, for the second group "Asbestos" the same applies, for the thirds group "Airplane" it only occurs in the keywords and the fourth group "Building Material" has "Building" somewhere in the keywords and not in the categories. Below the entire list of categories in your data: Appliance Recycling Asbestos Info / Removal Service Automobile and Boat Reuse Recycling Awards and Recognition Batteries Bicycle Reuse-Donation Book Recycling Cellular Telephone Reuse & Recycling Clean-Up Services Clothing, Linens & Shoe Reuse & Recycling-Drop-Off Locations Commercial Hazardous Waste Composting Composting Facilities/Landscape Waste Transfer Stations Computer Reuse & Recycling Construction & Demolition Recycling Cooking Oil/Grease Recycling & Trap Service Crayon Recycling Curbside Recycling Document Destruction/Shredding Donation Drum & Cylinder Disposal or Recycling Dry Cleaners (Green) Electronics Eye Glass Reuse Fire Extinguisher Refilling or Recycling Fluorescent Lights Food Donation Geothermal Energy Gift Cards Glass Recycling Processors Green Energy Supplier Green Roofing Firms Grocery Bag Hazardous Waste Disposal or Recycling Heating Oil Tank Removal Household Hazardous Waste (HHW) Landfill & Transfer Stations Lead-based Paint Concerns Manure Mattress Recycling Medical Waste - Sharps or Needles Metal Recycling Motor Oil - Bulk Motor Oil Recycling (DIY) Packaging Materials Pallet Reuse or Recycling Paper Recycling Plastic Carrier Strap Recycling Plastic Container Recycling Plastic Recycling Pop-Tab Recycling Printer/Toner Cartridge & Ribbon Recycling Propane Tank Disposal or Recycling Rechargeable Battery Recycling Equipment Recycling Processing Facilities Recycling and Waste Collection Companies Repair Services Reuse and Resale Shops Smoke Detectors Solar Energy Styrofoam or Polystyrene Recycling Tire Recycling Water Conservation Wind Energy ... and the list of keywords (where I notice many similar keywords and some seem to be cut off): Adding Machines Aerosol Cans (empty) Curbside Aerosol Cans (empty) Drop-Off Aerosol Products Air Conditioner Airplane Alu Aluminum / Aluminum Can Scrap Aluminum Can Curbside Aluminum Can Recycling Drop-Off Aluminum Foil Aluminum Foil Curbside Aluminum Foil Rec Aluminum Foil Recycli Aluminum Foil Recycling Drop-Off Answering Machines Antifreeze Appliance Asbestos Asphalt Automobile Automotive Batteries Automotive Fluid Awards (Eco-Award) Batteries - Rechargeable, BackUp, Lead-Acid Batteries - Single Use AA, AAA, C, D, 9-Volt Beverage Carrier Straps Bicycles Bo Boat Boat Batteries Book Cases Books - Hardcover Books - Hardcover Recycling Books - Softcover Recycling Brass Brick Building Construction & Demolition Debris Business Electronic Devices Button Batteries CB's/Two -way radios CD Players / Laser Disc Players CD ROM Drives CDs, DVDs CFLs Calculators Cameras Car Cardboard Cardboard & Chipboard Curbside Cardboard Boxes Carpet Carpet Padding Cassettes Cell Phone - Electronic Drop-Off Cell Phone - Retail Drop-Off Chemical Business Waste Chipboard Clean-Up of Properties Cleaning Chemicals Clothing Co Colored Paper Compact Fluorescent Compact Fluorescent Light Bulbs Compact Fluorescent Light Bulbs - HHW Drop-Off Compost Facilities/Landscape Waste Transfer Stations Composting, Vermi (worms) Compute Computer Recycling Computer Recycling Drop-Off Computer Reuse/Donation Concrete Construction Material Reuse Construction Paper Cooking Oil Copper Copy Machine Copy Machines Cords & Cables Cou Couch Cough Medicine Crayons Cubical Walls Curbside Recycling Cylinders DVD Machine Dehumidifier Demolition Material Reuse / Recycling Desks Di Dig Digital Cloc Digital Clocks Dirt Dishwasher Drain Cleaners Driveway Sealer Drums Dry Cleaning Dryer Drywall El Electronic Mice Electronics Energy Supplier Eye Glasses Fax Machines Fertilizers File Cabinets Fire Extinguishers Floppy Disks Fluorescent Light Bulbs Fluorescent Lights Food Donation Furniture & Office Equipment Garbage Collection Company Gasoline-Oil Mix Geothermal Gift Cards (merchant plastic money card) Glass Bottles and Jars Curbside Glass Bottles and Jars Drop-Off Glass Recycling Gravel Grease Recycling Grease Trap Services Grocery Bags Gym Shoes / Sneakers / HighTops HHW Ha Hand Held Games Hard Drives Hazard Hazardous Household Materials Hazardous Waste Services Heating Oil Tank Removal Household Chemicals Inhalers Inkjets (Ribbons) Iron Joysticks/Game controls Juice Boxes / Drink Pouches Drop-Off Juice Boxes / Drink Pouches Curbside Junk Mail Keyboards Landfills & Transfer Stations Landscape Material Recycling Latex Paint Lead Paint Lead-Acid Batteries Linens / Blankets / Tableclothes / Towels Lithium Batteries Magazines Manure Mattress and Box Springs Medical Waste Medicated Shampoo Medication (old or unwanted) Microwaves Modems Monitors Motor Oil Motor Oil - DIY Motor Oil in Bulk Drums Needles Newspaper Office Chairs Office Paper Oil-based Paint / Stain / Varnish Ointment / Medicated Ointment Oven Over-the-Counter Medication Packing Peanut Padding Pagers Palle Pallets Palm Organizers Paper (magazines, phone bks, etc Paper (magazines, phone bks, etc) Curbside Paper Recycling Paper shr Pills, Pharmaceuticals, Medication Plasti Plastic #1 (PET) Plastic #1 (PET) & #2 (HDPE) Containers Plastic #1, PET Plastic #2, HDPE Plastic #3, PVC Plastic #4, LDPE Plastic #5, PP Plastic #6, PS, Polystyrene Plastic #7, Other Cont Plastic #7, Other Container Plastics Pop-Tab Prescription Medication Printer/Toner Cartridges Printers Propane Tanks Purse or Handbag RV Rain Barrels Rain Garden Rechargeable Rechargeable Batteries Recreation Vehicle Recycling Collection Company Recycling Equipment Recycling Processing Facilities Refrigerator Reuse/Resale Roofing Materials Roofing- Green S Scanner Machines Scrap Metal Sharpening Services Sharps Shingles Shoes / Boots Shredding Shredding Services Six-Pack Rings Small Wind Smoke Detectors Sofa Soil Solar Energy Speakers/Stereo Systems Steel Scrap Stone Stove String Lights / Holiday String Lights Styrofoam Styrofoam Peanuts Sump Pump Sunscreen Tape Drives Telephone Televisions Textiles / Fabrics / Drapes / Curtains Thermometers (digital) Thermometers (glass) Tin / Steel Ca Tin / Steel Can Tin / Steel Cans (soup, coffee, etc) Drop-Off Tire Toys Truck Typewriters/Word Processors Used Oil VCR Machine VHS Tapes Video Game Players Vinyl Siding Vitamins Washer Waste Collection Company Water Heater Windows Wood-Untreated So, the main question remains, what are the final groups and what rules do you want to apply to assign a value to a group? If that can be resolved, we can create a script or expression. ... View more