Knotty Problem with Crosstab

[Charles Hoens]

I've been fighting over a knotty problem with using crosstab with a database. Essentially, I have a dbf with one field for the consumer name, one for a date and 6 fields of different expenses. I need to total these expenses by category and by year/month and by Consumer. (Essentially 3D)

I thought of writing 6 crosstabs with Consumer and date grouping - one for each of the expense categories, but it became messy when I tried to pull it together into one dbf. The messy part was the names A5 used for the date fields. I ended up writing 72 tablesum operations (6 categories X 12 months), but this isn't working either as I will need to capture new years as we go forward. Not to mention it was slow.

Is there another way?

Thanks for any ideas or pointers.

[Ray in Capetown]

Re: Knotty Problem with Crosstab

Charles, why crosstab?
Isnt this a typical summarization requirement?

Presenting it in those three basic totals, that's where you may be getting stuck - how would you like to present those three - does the client wish for statistcal analysis by those or comparisons?

I satisfied a requirement where a client wanted to have all answers to any sales comparison he could think up things like
which departments sold percentage of turnover in which categories between which months last year and those same months this year, then finding the result interesting, what of those were import and what local. I could show you an example of that - using dual periods and subtotals as a percentage of turnover but probably not what you want here. That is more trending combined with fiscal contribution done dynamically/interactively.

[Ted Giles]

Re: Knotty Problem with Crosstab

I'm with Ray on this one Charles.
I have a similar situation with multiple types of user and multiple financial transactions over differing time frames.
Summarise is what I used.

[Charles Hoens]

Re: Knotty Problem with Crosstab

OK. Even after all these years, I've only used summarize a handful of times. Let me look at it again. I'll let you know.

Thanks.

[Peter.Greulich]

Re: Knotty Problem with Crosstab

I have a dbf with one field for the consumer name, one for a date and 6 fields of different expenses. I need to total these expenses by category and by year/month and by Consumer. (Essentially 3D)

I don't know. But that's why I usually break this kind of structure down into a child table of expenses. It can be convenient to have a form w. all 6 fields, but if it's just one ($expense) field in it's own table (along w. category field, etc), then it becomes easier to handle problems like you are experiencing. What is it Al Buchholz always says - "Normalize till it hurts, denormalize till it works".

P.S. having said that, I'm sure my advice is of no use to you in your situation.

[Ted Giles]

Re: Knotty Problem with Crosstab

Charles, take a flask of coffee and some pain killers.
It is great when you get your head around it, but the testing is a bit problematic.
Suggest you start with 2 records and work up.

[Charles Hoens]

Re: Knotty Problem with Crosstab

Your advice Peter is always welcome. You've been a great help to me in the past. That's why I like this board so much. I don't know of another that is anywhere close as good as this one.

In this situation, given the way I have to present the data, I couldn't do what you suggested. However, if I don't come up with something, I might have to redo some major parts of this thing.

Thanks as always!

[Charles Hoens]

Re: Knotty Problem with Crosstab

OK. I took Peter's advice and created a script where I could scroll through my consumer records and create a dbf of Total Hours summarized. I also put some code in to flush out those consumers that had nothing to summarize, however I did put them into the TotalHours dbf, so anyone looking at it could see that there was a summary amount there.

I'd like to improve this to put in each month of the current year whether there were summary records or not in order to strictly conform to my clients request. In the past he had an Excel spreadhseet doing this.

Anyway, for anyone interested here's the code:

'Date Created: 11-Feb-2014 10:14:07 AM
'Last Updated: 11-Feb-2014 12:37:23 PM
'Created By  : Charles Hoens
'Updated By  : Charles Hoens
t=table.open("consumers")
s=table.open("tothours")
s.zap(.T.)
t.fetch_first()
dim global jac as c=""
'debug(1)
while .not. t.fetch_eof()
jac=alltrim(t.lastfirst)
	
tbl = table.open("timehours")
sum.db = "tHSumm.DBF"
sum.db_dd_delete = .T.
sum.result_overwrite = .T.
sum.order = "ALLTRIM(STR(YEAR(EDATE)))+cmonth(EDATE)"
sum.filter = "alltrim(consumer)='" + jac + "'"
sum.options = "I"
sum.fields = 8
sum.field1 = "alltrim(str(year(edate)))+cmonth(edate)"
sum.code1 = 0
sum.field2 = "\"Filter\""
sum.code2 = 16
sum.field3 = "Behaviorist"
sum.code3 = 1
sum.field4 = "Behaviorsupp"
sum.code4 = 1
sum.field5 = "Respite"
sum.code5 = 1
sum.field6 = "Mileage"
sum.code6 = 1
sum.field7 = "Activity"
sum.code7 = 1
sum.field8 = "Dayprog"
sum.code8 = 1
tbl.summarize()
'tbl.close()

NEXTT:
if reccount("thsumm")>0
r=table.open("thsumm")
r.fetch_first()
while .not. r.fetch_eof()
s.enter_begin()
s.consumer=t.lastfirst
s.yearmonth=r.alltrim_str_year_edate____cmonth_edate_
s.activity=r.total_activity
s.behavior_support=r.total_behaviorsupp
s.behaviorist=r.total_behaviorist
s.day_program=r.total_dayprog
s.mileage=r.total_mileage
s.respite=r.total_respite
s.enter_end()

r.fetch_next()
end while
r.close()
r.zap(.T.)
else
s.enter_begin()
s.consumer=t.lastfirst
s.yearmonth=0
s.activity=0
s.behavior_support=0
s.behaviorist=0
s.day_program=0
s.mileage=0
s.respite=0
s.enter_end()
end if
t.fetch_next()
end while
t.close()
s.close()
end