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!
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] );,
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.
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!
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] );,
thanks so much!