topic Re: Iterating Down Rows in Discussions

Iterating Down Rows

nqj — Fri, 19 May 2017 15:45:53 GMT

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!

Re: Iterating Down Rows

Wendy_Murphrey — Fri, 19 May 2017 16:23:57 GMT

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.

Re: Iterating Down Rows

nqj — Fri, 19 May 2017 16:29:06 GMT

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!

Re: Iterating Down Rows

Wendy_Murphrey — Fri, 19 May 2017 20:12:14 GMT

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] );,

Re: Iterating Down Rows

nqj — Mon, 22 May 2017 11:45:08 GMT

thanks so much!