Iterating Down Rows

[#1]

Hi,

I'm new to JMP/JSL but have experience with Java and am currently writing a program that needs to iterate through a column of address data and check if the address is already in the system; if it is, it should populate new columns with the latitude and longitude values. I'm using associative arrays and this is what I have so far:

NamesDefaultToHere(1); //all variables local
rawData = Open("Kevin-PriceAnalysis-DO Basis US thru April.jmp");
Current Data Table(rawData); //current data table set to list of hospitals
address = Column(rawData, "Full Address");
dt_latMap = Associative Array(address, rawData:Latitude); //maps unique full address to latitude
dt_longMap = Associative Array(address, rawData:Longitude); //maps unique full address to longitude
dt_coord = new Table("Coordinates",
New Column("Lat", Numeric, Continuous, Format("Lat DDD", 24, 14)),
New Column("Long", Numeric, Continuous, Format("Long DDD", 24, 14))
); //new data table of coordinates (empty)
For(i = 1, i <= N Rows(rawData), i++, //iterates through raw data, checks if contaied in map
If(Contains(dt_latMap, address[i]), //if yes updates coordinates
dt_coord:Lat = dt_latMap<<Get Value(address[i]);
dt_coord:Long = dt_longMap<<Get Value(address[i]),
!Contains(dt_latMap, address[i]), //if not, keep looping
continue();
);
);

I keep getting an "cannot set value for 'Lat' because row number (-1) is invalid. I thought it could be a problem with column naming but I tried changing the names of the columns. Any help would be greatly appreciated! Thanks!

[#2]

Hello,

It sounds like you have a column name and JSL variable name that are the same.  See the Preventing Column Name and Variable Name Conflicts section in the online documentation.

Hope this is helpful.

[#3]

Hi Wendy,

I've tried changing all column and variable names and am still having the same issue. I have since changed the code that populates the associative array as such:

For(j = 1, j<= N Rows(rawData),j++,
If(!Contains(dt_latMap, address[j]),
dt_latMap << Insert(address[j], rawData:Latitude[j]),
!Contains(dt_LongMap,address[j]),
dt_longMap << Insert(address[j], rawData:Longitude[j]);
)
);

but I am still getting the invalid row number, this time saying that the index is 0. Am I forgetting to set the current row somewhere? Thanks!

[#4 · Accepted solution]

I just stepped through your code using the San Francisco Crime.jmp table.  Sorry I didn't notice this before, but the dt_coord table has no rows.  When the criteria is met in your If(), first add a row.  Then use that row number as a subscript to your column reference.

		Contains( dt_latMap, address[i] ), //if yes updates coordinates
			dt_coord << Add Rows( 1 );
			r = N Rows( dt_coord );
			dt_coord:Lat[r] = dt_latMap << Get Value( address[i] );
			dt_coord:Long[r] = dt_longMap << Get Value( address[i] );,

[#5]

thanks so much!