Marketing cloud grabbing all zips for displaying on a cloud page

Question

I have a data extension that holds ~200k records each record has a zip code associated with it.

On my cloud page, I would like to display all of the UNIQUE zips as selectable options.

I have a solution in SSJS that allows only up to 2500 due to limitations of rows.retrieve()

is there anyway to grab the rest of them?

my code if interested

<script runat="server">
        Platform.Load("core","1");

        var makeArrhold =  DataExtension.Init("somedataextid");
        var makearrr2 = makeArrhold.Rows.Retrieve();

        var makeholder= [];

        for (var j = 0; j < makearrr2.length; j++)  {
          makeholder.push(makearrr2[j].Make_TXT);
        }

        var unqmakeholder = [];

        var negcheck;

        for(var b=0; makeholder.length > b; b++){

          negcheck = -1;

          for(var i=0; unqmakeholder.length > i; i++){


            negcheck = unqmakeholder[i].indexOf(makeholder[b]);

            if(negcheck != -1){
              negcheck = testresult; 
            }

          } 

          if(negcheck == -1){
            unqmakeholder.push(makeholder[b]); 

          }

        }


        Write("<UL>");
        for (var j = 0; j < unqmakeholder.length; j++)  {
          Write("<LI>" + unqmakeholder[j] + "</LI>");
        }
        Write("</UL>");



      </script>

this works to grab the unique values but will break once records reach over 2500'

any ideas?

Answer 1

I will suggest to create a data extension with all the unique zipcodes from your original DE. This can be built with Automation Studio and appropriate Query Activity. Let's call this new DE uniquezips

Set @zipcodes = 
LookupOrderedRows('uniquezips',DataExtensionRowCount('uniquezips'),'zip ASC','include','1')

You need to set a column with a consistent value across all rows (e.g. include), to overcome the need for a condition in LookupOrderedRows. This can be done in the query building uniquezips

You can also use this approach by Adam Spriggs, to deduplicate your rows directly in AmpScrip.

If SSJS is a hard requirement, there is a well-described solution here on StackExchange.