pemurray
asked on
THREE DIMENSIONAL ARRAY ?
Greetings All,
I have been trying to create a grid that show how many pickups we have in a given number of hours by county.
So the grid would look like:
BERGEN CUMBERLAND HUDSON MIDDLESEX MORRIS SOMERSET UNION WARREN
00:00 2 1 0 0 0 0 0 1
00:15 0 2 1 1 1 2 4 0
AND SO ON.
I CAN CONSTRUCT AN ARRAY OF COUNTIES
I CAN FIGURE OUT HOW TO GENERATE THE ROWS GOING OUT 12 HOURS
I JUST CAN'T FIGURE OUT HOW TO CREATE ONE ARRAY THAT I CAN CALL THAT WILL ALLOW ME TO DO A SETITEM ON EACH COLUMN/ROW IN THE GRID TO TOTAL THE INFORMATION CORRECTLY.
I AM WORKING WITH AN EXTERNAL DATAWINDOW. I WILL FIGURE OUT HOW TO TURN OFF THE UNNECCESARY COLUMNS LATER.
JUST CAN'T FIGURE OUT THE ARRAY AND THE ARRAY ADDITION.
HERE IS MY CODE:
I REALIZE IT IS A BIT SLOPPY, BUT THAT COMES NATURALLY FROM CONFUSION...
datetime start_date
datetime end_date
em_1.getdata(start_date)
em_2.getdata(end_date)
long setitemresult
long rows
long i
long newrow
long currentrow
time pu_time_bucket
string time_name
long startingbucket
time start_time
start_time = time(start_date)
pu_time_bucket = start_time
string county
integer pu
long bucketnumber
string currenttimebucket
string columnname
string dw_mod_string
string countycheck
string countyarray[]
integer loadgridarray[30,48]
rows = dw_2.retrieve(start_date,e nd_date)
//messagebox("dw_2 rows", string(rows))
startingbucket = dw_2.getitemnumber(1,"comp ute_bucket ")
currenttimebucket = dw_2.getitemstring(1,"comp ute_time_b ucket")
//messagebox("What we got!",string(startingbucke t) + " " + currenttimebucket)
//NOW CHANGE BUZZ THROUGH ALL OF THE COUNTIES, FILL THE ARRAY WITH THE COUNTY NAMES
long k
k = 0
long countyarraylength
STRING SANDEEP
//ASSEMBLE THE COLUMN COUNTY NAME ARRAY
for i = 1 to rows
countycheck = dw_2.getitemstring(i,"comp ute_county ")
countycheck = trim(countycheck)
if countycheck <> county then
k = k + 1
countyarray[k] = trim(countycheck)
// messagebox("Array Contents",countyarray[k])
end if
countyarraylength = k
county = countycheck
next
//QUESTIONS: HOW TO ASSEMBLE ONE ARRAY THAT HAS EVERYTHING I NEED
//COLUMN (E.G., EWR/JFK/BERGEN...)
//TIMEBUCKET (E.G., 00:00:00/00:15:00/00:30:00 ...)
//NUMBER OF PICKUPS PER TIME BUCKET PER COUNTY
//***safety***
//ASSEMBLE THE COLUMN COUNTY NAME ARRAY
//for i = 1 to rows
// countycheck = dw_2.getitemstring(i,"comp ute_county ")
// countycheck = trim(countycheck)
// if countycheck <> county then
// k = k + 1
// countyarray[k] = trim(countycheck)
//// messagebox("Array Contents",countyarray[k])
// end if
// countyarraylength = k
// county = countycheck
//next
//NOW USE THE COLUMN COUNTY NAME ARRAY TO BUZZ THROUGH THE ROWS AND ASSIGN VALUES ACCORDING TO TIME BUCKETS
long r
double compute_bucket
string compute_time_bucket
string time_bucket
long m
long c
m = countyarraylength
long temp-cr
For c = 1 to m
county = countyarray[c]
// messagebox("County Is",countyarray[c])
for r = 1 to rows
if trim(dw_2.getitemstring(r, "compute_c ounty")) = trim(county) then
time_bucket = dw_2.getitemstring(r,"comp ute_time_b ucket")
compute_bucket = dw_2.getitemnumber(r,"comp ute_bucket ")
messagebox("county and compute_time_bucket and compute_bucket is", "COUNTY:" + county + " " + "Time Bucket: " + time_bucket + " " + " Numbered Bucket:" + string(compute_bucket))
//this is all you need!!!!!!!!!!!!!!!!!!!!!! !!
messagebox("Value of the array BEFORE addition", string(loadgridarray[c,r]) )
//loadgridarray[c,r] = loadgridarray[c,r] + 1
temp-cr = loadgridarray[c,r]
messagebox("temp-cr is: ", string(temp-cr))
temp-cr = temp-cr + 100
loadgridarray[c,r] = temp-cr
messagebox("Value of the array AFTER addition", string(loadgridarray[c,r]) )
messagebox("LoadGridArray is now", "Array Coordinates: Column: " + string(c) + " Row: " + string(r) + "Array Value: " + string(loadgridarray[c,r] ))
end if
next
next
//CREATE THE NEW COLUMN NAMES
string dwmodreturncode
dw_1.SetRedraw(FALSE)
for i = 1 to (countyarraylength)
dw_mod_string = 'c' + string(i) + '_t' + ".Text=" +"'"+ countyarray[i] + "'"
dw_1.modify(dw_mod_string)
//MESSAGEBOX("dwMOD",dw_mo d_string + " " + dwmodreturncode)
next
dw_1.SetRedraw(TRUE)
//SET VALUES OF TIME IN THE LEFT MOST COLUMN (E.G. 14:15:00)
////COMMENT THIS OUT UNTIL WE KNOW HOW TO REFER TO THE NEW COLUMN NAMES
////NOW INSERT THE ROWS INTO DW_1 - 48 rows = 12 hours
for i = 1 to 48
newrow = dw_1.insertrow(0)
setitemresult = dw_1.SetItem(newrow,1,pu_t ime_bucket )
// messagebox("SetItem Values",string(newrow) + " " + currenttimebucket + " SetItem Result: " + string(setitemresult))
//?? pu_time_bucket = time(currenttimebucket)
pu_time_bucket = Relativetime(pu_time_bucke t, 900)
currenttimebucket = string(pu_time_bucket)
next
dw_1.accepttext()
for c = 1 to k
for r = 1 to rows
messagebox("Array Contents","column = " + string(c) + " Row = " + string(r) + " Array = " + string(loadgridarray[c,r]) )
next
next
THANKS FOR YOUR HELP IN ADVANCE...
PEM
I have been trying to create a grid that show how many pickups we have in a given number of hours by county.
So the grid would look like:
BERGEN CUMBERLAND HUDSON MIDDLESEX MORRIS SOMERSET UNION WARREN
00:00 2 1 0 0 0 0 0 1
00:15 0 2 1 1 1 2 4 0
AND SO ON.
I CAN CONSTRUCT AN ARRAY OF COUNTIES
I CAN FIGURE OUT HOW TO GENERATE THE ROWS GOING OUT 12 HOURS
I JUST CAN'T FIGURE OUT HOW TO CREATE ONE ARRAY THAT I CAN CALL THAT WILL ALLOW ME TO DO A SETITEM ON EACH COLUMN/ROW IN THE GRID TO TOTAL THE INFORMATION CORRECTLY.
I AM WORKING WITH AN EXTERNAL DATAWINDOW. I WILL FIGURE OUT HOW TO TURN OFF THE UNNECCESARY COLUMNS LATER.
JUST CAN'T FIGURE OUT THE ARRAY AND THE ARRAY ADDITION.
HERE IS MY CODE:
I REALIZE IT IS A BIT SLOPPY, BUT THAT COMES NATURALLY FROM CONFUSION...
datetime start_date
datetime end_date
em_1.getdata(start_date)
em_2.getdata(end_date)
long setitemresult
long rows
long i
long newrow
long currentrow
time pu_time_bucket
string time_name
long startingbucket
time start_time
start_time = time(start_date)
pu_time_bucket = start_time
string county
integer pu
long bucketnumber
string currenttimebucket
string columnname
string dw_mod_string
string countycheck
string countyarray[]
integer loadgridarray[30,48]
rows = dw_2.retrieve(start_date,e
//messagebox("dw_2 rows", string(rows))
startingbucket = dw_2.getitemnumber(1,"comp
currenttimebucket = dw_2.getitemstring(1,"comp
//messagebox("What we got!",string(startingbucke
//NOW CHANGE BUZZ THROUGH ALL OF THE COUNTIES, FILL THE ARRAY WITH THE COUNTY NAMES
long k
k = 0
long countyarraylength
STRING SANDEEP
//ASSEMBLE THE COLUMN COUNTY NAME ARRAY
for i = 1 to rows
countycheck = dw_2.getitemstring(i,"comp
countycheck = trim(countycheck)
if countycheck <> county then
k = k + 1
countyarray[k] = trim(countycheck)
// messagebox("Array Contents",countyarray[k])
end if
countyarraylength = k
county = countycheck
next
//QUESTIONS: HOW TO ASSEMBLE ONE ARRAY THAT HAS EVERYTHING I NEED
//COLUMN (E.G., EWR/JFK/BERGEN...)
//TIMEBUCKET (E.G., 00:00:00/00:15:00/00:30:00
//NUMBER OF PICKUPS PER TIME BUCKET PER COUNTY
//***safety***
//ASSEMBLE THE COLUMN COUNTY NAME ARRAY
//for i = 1 to rows
// countycheck = dw_2.getitemstring(i,"comp
// countycheck = trim(countycheck)
// if countycheck <> county then
// k = k + 1
// countyarray[k] = trim(countycheck)
//// messagebox("Array Contents",countyarray[k])
// end if
// countyarraylength = k
// county = countycheck
//next
//NOW USE THE COLUMN COUNTY NAME ARRAY TO BUZZ THROUGH THE ROWS AND ASSIGN VALUES ACCORDING TO TIME BUCKETS
long r
double compute_bucket
string compute_time_bucket
string time_bucket
long m
long c
m = countyarraylength
long temp-cr
For c = 1 to m
county = countyarray[c]
// messagebox("County Is",countyarray[c])
for r = 1 to rows
if trim(dw_2.getitemstring(r,
time_bucket = dw_2.getitemstring(r,"comp
compute_bucket = dw_2.getitemnumber(r,"comp
messagebox("county and compute_time_bucket and compute_bucket is", "COUNTY:" + county + " " + "Time Bucket: " + time_bucket + " " + " Numbered Bucket:" + string(compute_bucket))
//this is all you need!!!!!!!!!!!!!!!!!!!!!!
messagebox("Value of the array BEFORE addition", string(loadgridarray[c,r])
//loadgridarray[c,r] = loadgridarray[c,r] + 1
temp-cr = loadgridarray[c,r]
messagebox("temp-cr is: ", string(temp-cr))
temp-cr = temp-cr + 100
loadgridarray[c,r] = temp-cr
messagebox("Value of the array AFTER addition", string(loadgridarray[c,r])
messagebox("LoadGridArray is now", "Array Coordinates: Column: " + string(c) + " Row: " + string(r) + "Array Value: " + string(loadgridarray[c,r] ))
end if
next
next
//CREATE THE NEW COLUMN NAMES
string dwmodreturncode
dw_1.SetRedraw(FALSE)
for i = 1 to (countyarraylength)
dw_mod_string = 'c' + string(i) + '_t' + ".Text=" +"'"+ countyarray[i] + "'"
dw_1.modify(dw_mod_string)
//MESSAGEBOX("dwMOD",dw_mo
next
dw_1.SetRedraw(TRUE)
//SET VALUES OF TIME IN THE LEFT MOST COLUMN (E.G. 14:15:00)
////COMMENT THIS OUT UNTIL WE KNOW HOW TO REFER TO THE NEW COLUMN NAMES
////NOW INSERT THE ROWS INTO DW_1 - 48 rows = 12 hours
for i = 1 to 48
newrow = dw_1.insertrow(0)
setitemresult = dw_1.SetItem(newrow,1,pu_t
// messagebox("SetItem Values",string(newrow) + " " + currenttimebucket + " SetItem Result: " + string(setitemresult))
//?? pu_time_bucket = time(currenttimebucket)
pu_time_bucket = Relativetime(pu_time_bucke
currenttimebucket = string(pu_time_bucket)
next
dw_1.accepttext()
for c = 1 to k
for r = 1 to rows
messagebox("Array Contents","column = " + string(c) + " Row = " + string(r) + " Array = " + string(loadgridarray[c,r])
next
next
THANKS FOR YOUR HELP IN ADVANCE...
PEM
ASKER
Hi Batchakamal,
Thanks for your interest.
dw_2 reads a table, does a lot of work and gives me a set of rows as follows:
COUNTY PICKUP TIME BUCKET
BERGEN 03:00:00
BERGEN 03:30:00
CUMBERLAND 03:45:00
CUMBERLAND 05:00:00
ESSEX 05:30:00
HUDSON 06:00:00
HUDSON 04:00:00
HUDSON 05:30:00
HUDSON 07:00:00
HUNTERDON 05:00:00
MORRIS 04:30:00
MORRIS 07:00:00
MORRIS 07:00:00
WARREN 05:00:00
Once I get this list, I need to:
1) Change the name of the columns to the sorted names of the counties
2) Insert 48 rows (for twelve hours of 15 minute buckets
3) Add up the instance of each occurrence of the pickups (row)
4) change the value of the column where the county and time bucket intersect to show the total number of pick ups at that time
So finally, dw_1 looks like this:
BERGEN CUMBERLAND HUDSON MIDDLESEX MORRIS SOMERSET UNION WARREN
00:00 2 1 0 0 0 0 0 1
00:15 0 2 1 1 1 2 4 0
I hope that is less confusing than my code...
Thanks again,
Paul
Thanks for your interest.
dw_2 reads a table, does a lot of work and gives me a set of rows as follows:
COUNTY PICKUP TIME BUCKET
BERGEN 03:00:00
BERGEN 03:30:00
CUMBERLAND 03:45:00
CUMBERLAND 05:00:00
ESSEX 05:30:00
HUDSON 06:00:00
HUDSON 04:00:00
HUDSON 05:30:00
HUDSON 07:00:00
HUNTERDON 05:00:00
MORRIS 04:30:00
MORRIS 07:00:00
MORRIS 07:00:00
WARREN 05:00:00
Once I get this list, I need to:
1) Change the name of the columns to the sorted names of the counties
2) Insert 48 rows (for twelve hours of 15 minute buckets
3) Add up the instance of each occurrence of the pickups (row)
4) change the value of the column where the county and time bucket intersect to show the total number of pick ups at that time
So finally, dw_1 looks like this:
BERGEN CUMBERLAND HUDSON MIDDLESEX MORRIS SOMERSET UNION WARREN
00:00 2 1 0 0 0 0 0 1
00:15 0 2 1 1 1 2 4 0
I hope that is less confusing than my code...
Thanks again,
Paul
Try this stored procedure (MS SQL SERVER 2000)
I Hope it helps,
CREATE procedure sp_CrossTab
@tablename varchar(255), -- Table/View on which to perform the cross tab query.
@crosscolumn varchar(255), -- Attribute to be used as columns in the cross tab.
@crossrow varchar(255), -- Attribute to be used as rows in the cross tab.
@crossvalue varchar(255) -- Attribute to be used as value in the cross tab.
As
-- Work variables
declare
@sql varchar(8000), -- Hold the dynamically created sql statement
@colname varchar(255), -- The current column when building sql statement
@i smallint, -- know when we reached the last column (@i = @cols)
@cols smallint, -- Number of columns
@longest_col smallint, -- the len() of the widest column
@CrLf char(2)
-- Constants
declare
@max_cols_in_table smallint,
@max_col_name_len smallint,
@max_statement_len smallint,
-- @sql7 bit, -- 1 when version 7, 0 otherwise.
@err_severity int
set nocount on
set @max_cols_in_table = 255
set @max_statement_len = 8000
set @max_col_name_len = 128
set @err_severity = 11
set @CrLf = char(13) + char(10)
-- Check inputs
if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
raiserror ('Missing parameter(s)!',@err_severi ty,1)
return 0
end
-- Check for existence of the table.
if (not exists(select * from sysobjects where name like @tablename))begin
raiserror ('Table/View for crosstab not found!',@err_severity,1)
return 0
end
-- Don't check for columns because we may actually get an expression as the column name
-- prepare for future feature of checking database version to validate
-- inputs. Default to version 7
--set @sql7 = 1
--if (patindex('%SQL Server 7.%',@@version) = 0) begin
-- set @sql7 = 0
--end
-- Extract all values from the rows of the attribute
-- we want to use to create the cross column. This table
-- will contain one row for each column in the crosstab.
create table #crosscol (crosscolumn varchar(255))
set @sql = ' insert #crosscol Select Distinct ' + @crosscolumn +
' From ' + @tablename --+
--' Group By ' + @crosscolumn
--print @sql
exec (@sql)
set @cols = @@rowcount
if @cols > @max_cols_in_table begin
raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1 )
return 0
end
else begin
if @cols = 0 begin
raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1 )
return 0
end
else begin
-- Check if any of the data is too long to make it a name of a column
select @longest_col = max(len(convert(varchar(12 9),crossco lumn)))
from #crosscol
if @longest_col > @max_col_name_len begin
raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
return 0
end
else begin
-- All Validations OK, start building the dynamic sql statement
set @sql = ''
-- Use tmp table rows to create the sql statement for the crosstab.
-- each row in the table will be a column in the cross-tab
set @sql = 'select isnull(convert(varchar(255 ), ' + @crossrow + '),''Undefined'') As '
+ @crossrow + ', ' + @CrLf + space(4)
--set @sql = 'select ' + @crossrow + ', ' + char(13)
declare cross_sql cursor for
select crosscolumn
from #crosscol
order by crosscolumn
--print 'Sql cross statment: ' + @sql
open cross_sql
fetch next from cross_sql into @colname
-- Use "@i" to check for the last column. We need to input commas
-- between columns, but not after the last column
set @i = 0
while @@FETCH_STATUS = 0 begin
set @i = @i + 1
set @colname = isnull(@colname,'Undefined ')
set @crossvalue = isnull(@crossvalue, 0)
Set @sql = @sql + '''' +
convert(varchar(128), @colname) +
''' = sum(case convert(varchar(128), ' + @crosscolumn + ')'
+ char(13) + char(10) + space(8) +
' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '
if @i < @cols
set @sql = @sql + ', ' + @CrLf + space(4)
else
set @sql = @sql + @CrLf
fetch next from cross_sql into @colname
end
close cross_sql
deallocate cross_sql
set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow
if len(@sql) >= @max_statement_len begin
raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity, 1)
return 0
end
exec (@sql)
Select 'Sql' = @sql
set nocount off
return 1
end
end
end
GO
I Hope it helps,
CREATE procedure sp_CrossTab
@tablename varchar(255), -- Table/View on which to perform the cross tab query.
@crosscolumn varchar(255), -- Attribute to be used as columns in the cross tab.
@crossrow varchar(255), -- Attribute to be used as rows in the cross tab.
@crossvalue varchar(255) -- Attribute to be used as value in the cross tab.
As
-- Work variables
declare
@sql varchar(8000), -- Hold the dynamically created sql statement
@colname varchar(255), -- The current column when building sql statement
@i smallint, -- know when we reached the last column (@i = @cols)
@cols smallint, -- Number of columns
@longest_col smallint, -- the len() of the widest column
@CrLf char(2)
-- Constants
declare
@max_cols_in_table smallint,
@max_col_name_len smallint,
@max_statement_len smallint,
-- @sql7 bit, -- 1 when version 7, 0 otherwise.
@err_severity int
set nocount on
set @max_cols_in_table = 255
set @max_statement_len = 8000
set @max_col_name_len = 128
set @err_severity = 11
set @CrLf = char(13) + char(10)
-- Check inputs
if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
raiserror ('Missing parameter(s)!',@err_severi
return 0
end
-- Check for existence of the table.
if (not exists(select * from sysobjects where name like @tablename))begin
raiserror ('Table/View for crosstab not found!',@err_severity,1)
return 0
end
-- Don't check for columns because we may actually get an expression as the column name
-- prepare for future feature of checking database version to validate
-- inputs. Default to version 7
--set @sql7 = 1
--if (patindex('%SQL Server 7.%',@@version) = 0) begin
-- set @sql7 = 0
--end
-- Extract all values from the rows of the attribute
-- we want to use to create the cross column. This table
-- will contain one row for each column in the crosstab.
create table #crosscol (crosscolumn varchar(255))
set @sql = ' insert #crosscol Select Distinct ' + @crosscolumn +
' From ' + @tablename --+
--' Group By ' + @crosscolumn
--print @sql
exec (@sql)
set @cols = @@rowcount
if @cols > @max_cols_in_table begin
raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1
return 0
end
else begin
if @cols = 0 begin
raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1
return 0
end
else begin
-- Check if any of the data is too long to make it a name of a column
select @longest_col = max(len(convert(varchar(12
from #crosscol
if @longest_col > @max_col_name_len begin
raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
return 0
end
else begin
-- All Validations OK, start building the dynamic sql statement
set @sql = ''
-- Use tmp table rows to create the sql statement for the crosstab.
-- each row in the table will be a column in the cross-tab
set @sql = 'select isnull(convert(varchar(255
+ @crossrow + ', ' + @CrLf + space(4)
--set @sql = 'select ' + @crossrow + ', ' + char(13)
declare cross_sql cursor for
select crosscolumn
from #crosscol
order by crosscolumn
--print 'Sql cross statment: ' + @sql
open cross_sql
fetch next from cross_sql into @colname
-- Use "@i" to check for the last column. We need to input commas
-- between columns, but not after the last column
set @i = 0
while @@FETCH_STATUS = 0 begin
set @i = @i + 1
set @colname = isnull(@colname,'Undefined
set @crossvalue = isnull(@crossvalue, 0)
Set @sql = @sql + '''' +
convert(varchar(128), @colname) +
''' = sum(case convert(varchar(128), ' + @crosscolumn + ')'
+ char(13) + char(10) + space(8) +
' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '
if @i < @cols
set @sql = @sql + ', ' + @CrLf + space(4)
else
set @sql = @sql + @CrLf
fetch next from cross_sql into @colname
end
close cross_sql
deallocate cross_sql
set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow
if len(@sql) >= @max_statement_len begin
raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,
return 0
end
exec (@sql)
Select 'Sql' = @sql
set nocount off
return 1
end
end
end
GO
ASKER
Greetings Batchakamal,
Unfortunately, the system is a third-party system and I cannot put stored procedure into the database. I need to find a more passive solution.
I think I am mostly having trouble with the array arithmetic.
I have an array as follows (where c=column number and r=row number)
integer loadgridarray[30,48]
As I loop through the rows and find that there is a pickup that matches one of the time buckets and matches a county, I want to add 1 to the array. So if I have 3 pickups in Bergen county at 00:00:00, the value of loadgridarry[1,1] would = 3. Problem is that the command below does not seem to add 1 to the array at those coordinates.
loadgridarray[c,r] = loadgridarray[c,r] + 1
Once I get that fixes, I can loop through the array and do setitem in the grid for those coordinates.
Thanks for your help.
Paul
Unfortunately, the system is a third-party system and I cannot put stored procedure into the database. I need to find a more passive solution.
I think I am mostly having trouble with the array arithmetic.
I have an array as follows (where c=column number and r=row number)
integer loadgridarray[30,48]
As I loop through the rows and find that there is a pickup that matches one of the time buckets and matches a county, I want to add 1 to the array. So if I have 3 pickups in Bergen county at 00:00:00, the value of loadgridarry[1,1] would = 3. Problem is that the command below does not seem to add 1 to the array at those coordinates.
loadgridarray[c,r] = loadgridarray[c,r] + 1
Once I get that fixes, I can loop through the array and do setitem in the grid for those coordinates.
Thanks for your help.
Paul
I think your inner loop might be set up incorrectly.
IF I understand what you are trying to do, you want loadgridarray[2,3] to contain the number of pickups for the second location. Say your location list has {"Bergen", "Cumberland", "Hudson"} and your pickup times are {"00:00", "00:15", "00:30","00:45", "01:00"}. A 3 in loadgridarray[2,3] should indicate that there are 3 pickups for Cumberland at "00:30". However, your inner loop is set up as:
for r = 1 to rows
and as far as I can tell:
rows = dw_2.retrieve(start_date,e nd_date).
Meaning that your "r" value isn't going to be the pickup time, but the "index" of the pickup entry in dw_2. I don't know off the top of my head how to fix this, but I would guess you need a list of the valid "time buckets" to use as your "r" index.
IF I understand what you are trying to do, you want loadgridarray[2,3] to contain the number of pickups for the second location. Say your location list has {"Bergen", "Cumberland", "Hudson"} and your pickup times are {"00:00", "00:15", "00:30","00:45", "01:00"}. A 3 in loadgridarray[2,3] should indicate that there are 3 pickups for Cumberland at "00:30". However, your inner loop is set up as:
for r = 1 to rows
and as far as I can tell:
rows = dw_2.retrieve(start_date,e
Meaning that your "r" value isn't going to be the pickup time, but the "index" of the pickup entry in dw_2. I don't know off the top of my head how to fix this, but I would guess you need a list of the valid "time buckets" to use as your "r" index.
ASKER
Greetings, thanks for your assistance.
I have an extra computed field that I can use for r.
I think my problem is if loadgridarray[2,3] = 1 and I want to add one more to it, how do I do that?
I don't thinkg that **loadgridarray[2,3] = loadgridarray[2,3] + 1** worked.
Thanks!!
Paul
I have an extra computed field that I can use for r.
I think my problem is if loadgridarray[2,3] = 1 and I want to add one more to it, how do I do that?
I don't thinkg that **loadgridarray[2,3] = loadgridarray[2,3] + 1** worked.
Thanks!!
Paul
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is little confusing, when I go through the codes.
Instead, Just explain me the business problem.
I can help you in finding the solution of that problem.
Just give me the table structure that you have (for dw_2) , and explain me, what do you required from that?
By mean time, I will go through your code, and if I can, sure I will help and reply for this.
Bye