I've written code to produce a crosstab. I have not used the built in crosstab operation because I need to calculate the number of openings I have in each class for each level, so it subtracts the number registered from the maximum number. The code works but it takes about 3 minutes to run. The table has about 28,000 records. It's a mapped table. If someone would tell me how I can order or filter it to make it run faster, I'd appreciate it. I do not need the crosstab on the whole table. The range I've used in this example Lesson_num 1468-1495 is plenty.
Thank you.
Here's the code I've written:
'Date Created: 19-Feb-2014 03:32:13 PM
'Last Updated: 19-Feb-2014 03:32:13 PM
'Created By : Rob
'Updated By : Rob
dim tbl as p
tbl=table.open("lessonswimmer")
tbl.order("lesson_num")
dim v012 as n
dim v34 as n
dim v56 as n
dim v710 as n
dim vtot as n
dim vlesnum as n
dim v012max as n
dim v34max as n
dim v56max as n
dim v710max as n
v012max=12
v34max=8
v56max=3
v710max=3
vlesnum=1468
while vlesnum>=1468 .and. vlesnum<=1495
v012=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. totstat<3")
v34=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. (totstat>=3 .and. totstat<=4)")
v56=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. (totstat>=5 .and. totstat<=6)")
v710=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. (totstat>=7 .and. totstat<=10)")
vtot=v012+v34+v56+v710
dim tbl as p
tbl=table.open("openings")
tbl.enter_begin()
tbl.lessonnum=vlesnum
tbl.level012=v012max-v012
tbl.level34=v34max-v34
tbl.level56=v56max-v56
tbl.level710=v710max-v710
tbl.total=vtot
tbl.enter_end()
tbl.close()
vlesnum=vlesnum+1
end while
end
Thank you.
Here's the code I've written:
'Date Created: 19-Feb-2014 03:32:13 PM
'Last Updated: 19-Feb-2014 03:32:13 PM
'Created By : Rob
'Updated By : Rob
dim tbl as p
tbl=table.open("lessonswimmer")
tbl.order("lesson_num")
dim v012 as n
dim v34 as n
dim v56 as n
dim v710 as n
dim vtot as n
dim vlesnum as n
dim v012max as n
dim v34max as n
dim v56max as n
dim v710max as n
v012max=12
v34max=8
v56max=3
v710max=3
vlesnum=1468
while vlesnum>=1468 .and. vlesnum<=1495
v012=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. totstat<3")
v34=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. (totstat>=3 .and. totstat<=4)")
v56=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. (totstat>=5 .and. totstat<=6)")
v710=tablecount("lessonswimmer","lesson_num="+vlesnum +" .and. (totstat>=7 .and. totstat<=10)")
vtot=v012+v34+v56+v710
dim tbl as p
tbl=table.open("openings")
tbl.enter_begin()
tbl.lessonnum=vlesnum
tbl.level012=v012max-v012
tbl.level34=v34max-v34
tbl.level56=v56max-v56
tbl.level710=v710max-v710
tbl.total=vtot
tbl.enter_end()
tbl.close()
vlesnum=vlesnum+1
end while
end