Hi,

I am trying to find the min Dealer_cost and also count the number of fields for each country for the cars other than TRIUMPH


ENGLAND JAGUAR 7,427
11,194
JENSEN 14,940
TRIUMPH 4,292

Here the min DEALER_COST should be 7427 but not 4292 and COUNT should be 4.

I am using CNT.ENGLAND and MIN.DEALER_COST to find the values but I am not able to get both the values correctly. I also tried creating 2 different files and joining them but didnt work. can you please suggest any other way I can get both the values accurately.

Thanks in Advance
PVP

This message has been edited. Last edited by: Kerry,

Would this do the trick?

  
TABLE FILE CAR
SUM MIN.DEALER_COST CNT.CAR
BY COUNTRY 
WHERE CAR NE 'TRIUMPH'
END
-RUN

Thanks Microfich.. But that would return the Count value as 3 for the above example..

Can you create two hold files:

TABLE FILE CAR
SUM MIN.DEALER_COST AS MIN_COST
BY CAR
WHERE CAR NE 'TRIUMPH'
ON TABLE HOLD AS HOLDMIN
END

TABLE FILE CAR
SUM CNT.CAR AS CAR_CNT
BY CAR
WHERE CAR NE 'TRIUMPH'
ON TABLE HOLD AS HOLDCNT
END

Then use FOCUS and do something like:

TABLE FILE HOLDMIN
PRINT MIN_COST
ON TABLE HOLD AS HOLDCMB
MORE
FILE HOLDCNT
END

Then you should be able to report on the HOLDCMB as one table.

Thanks Peters.. your solution sounds much simpler...

here is what I did.. It is a bit complicated with many joins..

 
TABLE FILE CAR
SUM
CNT.DEALER_COST AS 'COUN'
BY COUNTRY
ON TABLE HOLD AS HOLD2 FORMAT FOCUS INDEX COUNTRY
END


JOIN COUNTRY IN CAR TO COUNTRY IN HOLD2

DEFINE FILE CAR
DEALERCOST2/I10 = IF COUN EQ 1 THEN DEALER_COST
	        ELSE IF COUN NE 1 AND CAR IS 'TRIUMPH' THEN 999999999
		ELSE IF COUN NE 1 AND CAR NE 'TRIUMPH' THEN DEALER_COST;
END

TABLE FILE CAR 
SUM
MIN.DEALERCOST2 AS 'MINVAL'
BY COUNTRY 
ON TABLE HOLD AS HOLDVAL FORMAT FOCUS INDEX COUNTRY
END

JOIN COUNTRY IN CAR TO COUNTRY IN HOLDVAL

TABLE FILE CAR
PRINT
DEALER_COST
MINVAL
COUN
BY COUNTRY 
BY CAR
END 

 

It had one more condition I forgot to mention.. If the country has only one type of car and that is 'TRIUMPH' then the MINVAl can be the DEALER_COST of the car but if there are more than one and the country has 'TRIUMPH' then the Minimum of other cars should be considered...

This message has been edited. Last edited by: pvparuc,

You can achieve what you currently have in one pass - although yours does not work because it does not pass COUN to the final TABLE request.

This will also satisfy your requirement that if an entity only contains the sub item to be excluded then the value should be used. Exchange TRIUMPH for PEUGEOT to test.

TABLE FILE CAR
  SUM CNT.DEALER_COST NOPRINT
   BY COUNTRY
PRINT COMPUTE MIN_DCOST/D20 = IF COUNTRY NE LAST COUNTRY THEN DEALER_COST
                         ELSE IF COUNTRY NE LAST COUNTRY AND CAR EQ 'TRIUMPH' THEN 99999999
                         ELSE IF CAR     EQ 'TRIUMPH' THEN LAST MIN_DCOST 
                         ELSE IF DEALER_COST LT LAST MIN_DCOST THEN LAST MIN_DCOST
                         ELSE LAST MIN_DCOST;
      COMPUTE CAR_COUNT/I9 = CNT.DEALER_COST;
   BY COUNTRY
   BY CAR
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  GRID=OFF, SIZE=10, $
ENDSTYLE
END

T

This message has been edited. Last edited by: Tony A,

thanks Tony.. but my program worked and the value of COUN is passed to the final table request as the first HOLD file is joined to the CAR file.. and the 99999999 I gave is just a dummy value to be higher than all the other DEALER_COST so that it will never be the minimum...
As per your Idea.. I tried doing it the same way but got the following O/P depending on the data


COUNTRY CAR DLRCOST MINVAL
----------------------------------
W GERMANY BMW 5,800 5800
6,000 5800
10,000 5800
11,000 5800
8,300 5800
8,400 5800
AUDI 5,063 5063

But all the MINVAl have to be the minimum value of 5063.. so I had to go to MIN.DEALER_COST..

I gather you want to exclude Triumph from the min(cost) calculation, but include it in the count. Simple enough:

DEFINE FILE CAR
  DEALER_COST MISSING ON =IF CAR EQ 'TRIUMPH' 
     THEN MISSING 
     ELSE DEALER_COST;
END
TABLE FILE CAR
SUM
 MIN.DEALER_COST
 CNT.BODYTYPE
BY COUNTRY
END

quote:

and the 99999999 I gave is just a dummy value to be higher than all the other DEALER_COST so that it will never be the minimum

Mathematics 101!

quote:

depending on the data

Which is why I use COMPUTE instead of DEFINE as it is applied to the internal matrix (sorted) as opposed to the data as it is extracted (possibly unsorted).

If you want to use DEFINEd values for DEALER_COST when CAR EQ 'TRIUMPH' then it is still possible to achieve your result in one pass which, depending upon the size of your data extraction, could provide huge benefits through no double data handling. -

DEFINE FILE CAR
  DEAL_COST/D20 = IF CAR EQ 'TRIUMPH' THEN 99999999 ELSE DEALER_COST;
END
TABLE FILE CAR
-* these two lines are important because they affect the internal matrix
-* to provide the correct values at the required sort level
-* e.g. at COUNTRY
  SUM CNT.DEALER_COST NOPRINT
      MIN.DEAL_COST   NOPRINT
   BY COUNTRY
PRINT DEALER_COST
-* These two COMPUTEs bring the previously determined values which are NOPRINTed.
      COMPUTE MINVAL/D20   = MIN.DEAL_COST;
      COMPUTE CAR_COUNT/I9 = CNT.DEALER_COST;
   BY COUNTRY
   BY CAR
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  GRID=OFF, SIZE=10, $
ENDSTYLE
END

As you noticed, if any of the components are not replicated then the results do not come.

T