Help with Summarization Please

[Graham Wickens]

I am trying to summarize a table (relevant fields Date,Reg and As) (First try at Summarization)
for each DATE I want to summarize :
Total number of Entries (ie REG) and Total of Expression "AS is Blank" for each DATE
I can do both separately, but not together, so what am I missing ? Can it be done or do I indeed have to do it twice?

Field/Expression Summarization Filter
DATE Group by
Reg Count
or
As Count is blank

thx

I can create this in a report, but want to include it on my form

daily_Total = count(reg,grp->Date)
daily_Needs = total(if(isblank("as"),1,0),grp->Date)

[Al Buchholz]

Have you looked at the summarize operation to build another table with that info that can be linked to?

Is this a multi user system or your single user aircraft system?

Can do either but may be handled slightly differently.

[Graham Wickens]

Hi Al,

its my single User Aircraft System.

I sort of got it working, probably the long way around with the following code using the Daily Totals Report

if a_dlg_button = "Daily_Totals_Button_Pushed" then
a_dlg_button = ""

SaveReport_Filename = filename_decode("[txtPath]Sywell_Daily_Totals.txt")
:Report.SaveAs("Sywell_Daily_Totals","TXT","","",SaveReport_Filename,.F.)
fp = file.open(SaveReport_FileName,FILE_RW_SHARED)
DailyText = ""
WHILE .not. fp.eof()
text = fp.read_line()
DailyText = DailyText+text+crlf()
END WHILE
fp.close()
ShowDailyTotalsFlag = .t.

end if

but would prefer to do it without the screen blinking at me :)

[Graham Wickens]

I now have two summaries as follows:

summarize_db_name1 = filename_decode("test1.DBF")
tbl = table.open("historic_logging")
sum.db = summarize_db_name1
sum.db_dd_delete = .F.
sum.result_overwrite = .F.
sum.order = "SEEN_WHEN+SEEN_H+SEEN_WHERE"
sum.filter = "BETWEEN(SEEN_WHEN,CTOD(\"03/09/2021\"),CTOD(\"04/09/2021\")).AND.(SEEN_WHEN=FIRST_WHEN)"
sum.options = "I"
sum.fields = 2
sum.field1 = "SEEN_WHERE"
sum.code1 = 0
sum.field2 = "SEEN_WHERE"
sum.code2 = 2
sum.field3 = "Seen_where"
sum.code3 = 1
tbl.summarize()
tbl.close()
file_add_to_db(summarize_db_name)

which gives me

Seen_Where ,Count_Seen_Where
OXFORD-KIDLINGTON ,13
SYWELL ,98
SYWELL-MUSEUM ,2
SYWELL-OTT ,4
HUSBANDS BOSWORTH ,3
SYWELL ,59
WESTON-ON-THE-GREEN ,2
OXFORD-KIDLINGTON ,7

and

summarize_db_name2 = filename_decode("test2.DBF")
tbl = table.open("historic_logging")
sum.db = summarize_db_name2
sum.db_dd_delete = .F.
sum.result_overwrite = .F.
sum.order = "SEEN_WHEN+SEEN_H+SEEN_WHERE"
sum.filter = "BETWEEN(SEEN_WHEN,CTOD(\"03/09/2021\"),CTOD(\"04/09/2021\"))"
sum.options = "I"
sum.fields = 2
sum.field1 = "SEEN_WHERE"
sum.code1 = 0
sum.field2 = "SEEN_WHERE"
sum.code2 = 2
sum.field3 = "Seen_where"
sum.code3 = 1
tbl.summarize()
tbl.close()
file_add_to_db(summarize_db_name)

which gives me

Seen_Where ,Count_Seen_Where
OXFORD-KIDLINGTON ,30
SYWELL ,435
SYWELL-MUSEUM ,6
SYWELL-OTT ,4
HUSBANDS BOSWORTH ,11
SYWELL ,474
WESTON-ON-THE-GREEN ,6
OXFORD-KIDLINGTON ,24
ASTON DOWN ,2

1) How do I combine them to get one result ?

Seen_Where ,Count_Seen_Where
OXFORD-KIDLINGTON ,13,30
SYWELL ,98,435
SYWELL-MUSEUM 2,6
SYWELL-OTT ,4,4
HUSBANDS BOSWORTH ,3,11
SYWELL ,59,474
WESTON-ON-THE-GREEN ,2,6
OXFORD-KIDLINGTON ,2,24
ASTON DOWN ,0,2

2) How do I then get a total for each ie 183 and 992 ?

[armasoft]

Graham, you need to run a 'join' against the 2 tables...

target_table_name = filename_decode("[PathAlias.ADB_Path]\test3.dbf")
a_tbl = table.open("test1")
join.t_db = "test2"
join.o_file = target_table_name
join.m_key = "seen_where"
join.t_key = "seen_where"
join.m_filter = ""
join.t_filter = ""
join.m_count = 3
join.delete_o_dd = .T.
join.m_field1 = "seen_where"
join.m_field2 = "count_seen_where"
join.m_field3 = "@Test2->seen_where"
a_tbl.join()
a_tbl.close()
file_add_to_db(target_table_name)

[Graham Wickens]

I tried that , but it does not do what I want, as I have Visited SYWELL and OXFORD-KIDLINGTON twice in the specified date range, the second occurence of each contains the same values as the first. I have to some how make each SEEN_WHERE unique for the join to work properly.

[Graham Wickens]

thanks "Armasoft" for pointing me in the right direction.

I had to code my own custom "JOIN"

tb1 = table.open(summarize_db_name1)
tb2 = table.open(summarize_db_name2)
tb3 = table.open("tripTotalsTemp3")
tb3.delete_range(".t.")
while .not. tb1.fetch_eof()
tb3.enter_begin()
tb3.seen_where = tb1.seen_where
tb3.count_needs = tb1.count_seen_where
tb3.count_Total = tb2.count_seen_where
tb3.enter_end(.t.)
tb1.fetch_next()
tb2.fetch_next()
end while
tb1.close()
tb2.close()
tb3.close()

which gave me the required result:

OXFORD-KIDLINGTON,13,30
SYWELL,100,444
SYWELL-MUSEUM,2,6
SYWELL-OTT,4,4
HUSBANDS BOSWORTH,3,11
SYWELL,59,474
WESTON-ON-THE-GREEN,2,6
OXFORD-KIDLINGTON,7,24

[armasoft]

I've spent many an hour watching people jumping out of perfectly serviceable aircraft when the other side of the road from WOTG having my old bin cleaning kit fixed.
Think I prefer my new spot near Fairford watching the U2 take off most mornings - ear defenders recommended.

[Graham Wickens]

True! Same U-2 everyday though!