hi
i'm having a little problem with my counts.
this is the code:

DEFINE FILE CAR
CNTR/I1=1;
END

TABLE FILE CAR
PRINT CAR
BY COUNTRY
BY MODEL


ON COUNTRY SUBFOOT
"<ST.CNTR"
END

right now, it's showing me the total count of the MODEl per each country, but i want to see the count for each CAR per country (keeping the same sort order)

help please

Thanks!

quote:

DEFINE FILE CAR
CNTR/I1=1;
END

TABLE FILE CAR
PRINT CAR
BY COUNTRY
BY MODEL


ON COUNTRY SUBFOOT
"END

what is the difference?

you see 4, 1, 4, 2 , 7
thats the number of cars you have but also the number of models.

Frank

ok, i dont know if i explained myself incorrectly... if you look at Italy, there's two types of cars, and 4 types of models.
on the subfoot count it says "4", but i want it to say "2"......

so i want to count the cars, not the models

COMPUTE not DEFINE.

You are working at summary level not detail.

TABLE FILE CAR
PRINT
COMPUTE CNTR/I1= IF CAR EQ LAST CAR THEN 0 ELSE 1; NOPRINT
CAR
BY COUNTRY
BY CAR NOPRINT
BY MODEL
ON COUNTRY SUBFOOT

 "<ST.CNTR"
 

END

Sorry had toi edit because of the tag.

This may do the trick if you're looking for a count of unique cars per country:

DEFINE FILE CAR
CNTR/I9 = IF CAR EQ LAST CAR THEN CNTR + 1 ELSE 1;
END

TABLE FILE CAR
SUM
CAR
BY COUNTRY

ON COUNTRY
SUBFOOT
"<ST.CNTR"
SUM
CAR
BY COUNTRY
BY MODEL
END

i knew that!
hehe

Thanks!!!

The problem is the Define and the BY. You define CNTR as 1 which means that for every line that appears the value is 1. Then you sort by count and by model. Since you sort by model it forces a line for each model line and you get 4 instead of 3.
If you do a multiverb set then you get what you want because it first calculates counter by car then prints all the models.
DEFINE FILE CAR
CNTR/I1 WITH CAR=1;
END
TABLE FILE CAR
SUM CNTR NOPRINT
BY COUNTRY
SUM CAR
BY COUNTRY
BY MODEL
ON COUNTRY SUBFOOT
"END

Someone should really explain to the world the difference between

DEFINE and COMPUTE.

DEFINE works on the input row level.

COMPUTE works on the report matrix level.

It’s something that seems to be forgotten quite frequently.

Prime examples if the averages are totally screwy I bet you’re
summing them and not calculating them at the correct point.

I love this list. It shows so many ways to solve problems. Only issue with define that I can see is if the incoming file is not in the order you are doing your define with. I've been bit by that one. Added the computed item as other wise the subfoot is running total for some reason.
FUNKY BUT WORKS

TABLE FILE CAR
SUM CNT.CAR NOPRINT
COMPUTE CARCNT/I4 = C1; NOPRINT
BY COUNTRY
PRINT CAR
BY COUNTRY
BY MODEL

ON COUNTRY SUBFOOT
"CAR COUNT FOR COUNTRY <COUNTRY IS <CARCNT"
END

Not FUNKY,

That's the point.

The ST. in my example was actually not needed because a report matrix item
(i.e. COMPUTE) is by definition a total at it's level.

DEFINE / COMPUTE

A bit of training, a lot of frustration, a lot of practice.

The basic rules work 'almost every time', BUT we all forget.

The CAR file is a Hierarchical data base. The value of a record count will vary with it position in the data base. The “with” in define tells FOCUS where in the structure you want the defined field. This show the result of using with for different position in a data base. The multi-verb allows you to see the result in a single table request.

DEFINE FILE CAR
CTR_COUNTRY/I3 WITH COUNTRY = 1;
CTR_CAR/I3 WITH CARS = 1;
CTR_MODEL/I3 WITH MODEL = 1;
END

TABLE FILE CAR
SUM
CTR_COUNTRY AS 'CTR,COUNTRY'
BY COUNTRY

SUM CTR_CAR AS 'ctr,car'
BY COUNTRY
BY CARS

SUM CTR_MODEL AS 'ctr,model'
BY COUNTRY
BY CARS
BY MODEL
ON TABLE SUMMARIZE
END
The output:

PAGE 1


COUNTRY CTR
COUNTRY
CAR ctr
car
MODEL ctr
model
ENGLAND 1 JAGUAR 1 V12XKE AUTO 1
XJ12L AUTO 1
JENSEN 1 INTERCEPTOR III 1
TRIUMPH 1 TR7 1
FRANCE 1 PEUGEOT 1 504 4 DOOR 1
ITALY 1 ALFA ROMEO 1 2000 4 DOOR BERLINA 1
2000 GT VELOCE 1
2000 SPIDER VELOCE 1
MASERATI 1 DORA 2 DOOR 1
JAPAN 1 DATSUN 1 B210 2 DOOR AUTO 1
TOYOTA 1 COROLLA 4 DOOR DIX AUTO 1
W GERMANY 1 AUDI 1 100 LS 2 DOOR AUTO 1
BMW 1 2002 2 DOOR 1
2002 2 DOOR AUTO 1
3.0 SI 4 DOOR 1
3.0 SI 4 DOOR AUTO 1
530I 4 DOOR 1
530I 4 DOOR AUTO 1
TOTAL 5 10 18


Note the counter vary are 5, 10, and 18. These values may vary with the records in the CAR file.

Opps!! Sorry the report is gibberish. Had HTML disabled.

PAGE 1
COUNTRY	CTR COUNTRY	CAR	ctr car	MODEL	ctr model
ENGLAND	1	JAGUAR	1	V12XKE AUTO	1
				XJ12L AUTO	1
		JENSEN	1	INTERCEPTOR III	1
		TRIUMPH	1	TR7	1
FRANCE	1	PEUGEOT	1	504 4 DOOR	1
ITALY	1	ALFA ROMEO	1	2000 4 DOOR BERLINA	1
				2000 GT VELOCE	1
				2000 SPIDER VELOCE	1
		MASERATI	1	DORA 2 DOOR	1
JAPAN	1	DATSUN	1	B210 2 DOOR AUTO	1
		TOYOTA	1	COROLLA 4 DOOR DIX AUTO	1
W GERMANY	1	AUDI	1	100 LS 2 DOOR AUTO	1
		BMW	1	2002 2 DOOR	1
				2002 2 DOOR AUTO	1
				3.0 SI 4 DOOR	1
				3.0 SI 4 DOOR AUTO	1
				530I 4 DOOR	1
				530I 4 DOOR AUTO	1
TOTAL	5		10		18

DEFINE is not the correct option for what is wanted for a very simple reason
It processes every input row, that means if you have 100k rows it performs 100k
calculations, that's a performance hit.
For efficiency these calculations should be done as a COMPUTE as that operates at output level.

In a multi-verb request you do not need to use WITH as the report matrix has already
been rolled up to the required level.


TABLE FILE CAR
SUM
COMPUTE CTR_COUNTRY/I3 = 1;AS 'CTR,COUNTRY'
BY COUNTRY

SUM
COMPUTE CTR_CAR/I3 = 1; AS 'ctr,car'
BY COUNTRY
BY CARS

SUM
COMPUTE CTR_MODEL/I3 = 1; AS 'ctr,model'
BY COUNTRY
BY CARS
BY MODEL
ON COUNTRY SUBTOTAL
END

JG

Whilst you are correct that when using SUMmed values, a COMPUTE can be more efficient, in this case there is no difference in the number of calculations done between a DEFINE and a COMPUTE, as you are retrieving every value into the internal matrix anyway.

The WITH option triggers the DEFINE on retrieving every instance of that field, the COMPUTE is triggered on the Internal Matrix value for that field. For COUNTRY both are triggered 5 times.

There is no 'efficient' way of doing this, though for a FOCUS db using SUM CNT.field might just have an edge because of the way it builds an IM.

TABLE FILE CAR
SUM CNT.COUNTRY
SUM CNT.CAR
SUM CNT.MODEL
END

Or

TABLE FILE CAR
SUM CNT.COUNTRY
SUM CNT.CAR BY COUNTRY
SUM CNT.MODEL BY COUNTRY BY CAR
ON COUNTRY SUBTOTAL
END

This message has been edited. Last edited by: Alan B,