Please consider the following code where I am defining the most expensive and least expensive retail cost model in each country

  
TABLE FILE CAR
SUM
	FST.MODEL WITHIN COUNTRY AS 'Most Expensive Model' 
	LST.MODEL WITHIN COUNTRY AS 'Least Expensive Model' 
BY COUNTRY 
BY MODEL 
BY HIGHEST RETAIL_COST 
END

When I try to create compute fields off the least and most expensive models, I receive the error: "Unknown error occurred. Agent on reporting server EDASERVE may have crashed or request was halted by the operator. Please investigate reporting server log. "

  
TABLE FILE CAR
SUM
	COMPUTE MEM/A50 = FST.MODEL; WITHIN COUNTRY
	COMPUTE LEM/A50 = LST.MODEL; WITHIN COUNTRY
BY COUNTRY 
BY MODEL 
BY HIGHEST RETAIL_COST 
ON TABLE PCHOLD FORMAT HTML
END

I would like to ONLY print the most and least expensive car models for each country. I know I can throw this into a hold file and rejoin the hold table back to car, but is there a cleaner solution?

I would like to be able to use "WHERE TOTAL" Model equals Most expensive or least expensive.

Does anyone know how to do this?

Thank you

This message has been edited. Last edited by: Joey Sandoval,

That's not going to work.

The documentation for WITHIN states:
A maximum of two WITHIN phrases can be used per display field. If one WITHIN phrase is used, it must act on a BY phrase. If two WITHIN phrases are used, the first must act on a BY phrase and the second on an ACROSS phrase.

Please refer to the old thread, which might help you.

http://forums.informationbuild...467084326#1467084326

Atturhari,

This code works. Although, I did have the "BY HIGHEST RETAIL_COST" in the wrong spot in my previous post.

 
TABLE FILE CAR
SUM
	FST.MODEL WITHIN COUNTRY AS 'Most Expensive Model' 
	LST.MODEL WITHIN COUNTRY AS 'Least Expensive Model' 
BY COUNTRY 
BY HIGHEST RETAIL_COST 
BY CAR
BY MODEL 
END 

How would you code this procedure to only show the least / most expensive car model per country?

Hi Joey,

How about this:

TABLE FILE CAR
SUM
FST.MODEL WITHIN COUNTRY AS 'Most Expensive Model'
LST.MODEL WITHIN COUNTRY AS 'Least Expensive Model'
BY COUNTRY
BY HIGHEST 1 RETAIL_COST NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
END

~Jim

Jim, thank you. That works nicely in this example using the car file. However, I tried applying this idea to a procedure I have with our customer data, and it is just taking too long to process for some reason. I guess there is just too much data and this method is not as effecient as I thought it would be.

Hi Joey,

What happens if you try something like this? How many records are there in your "HLD_CAR" hold file?

TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
RETAIL_COST
ON TABLE HOLD AS HLD_CAR
END

TABLE FILE HLD_CAR
SUM
FST.MODEL WITHIN COUNTRY AS 'Most Expensive Model'
LST.MODEL WITHIN COUNTRY AS 'Least Expensive Model'
BY COUNTRY
BY HIGHEST 1 RETAIL_COST NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
END

Let me know if this helps.
~Jim

Try
TABLE FILE CAR
SUM
FST.MODEL AS 'Most Expensive Model'
LST.MODEL AS 'Least Expensive Model'
BY COUNTRY

BY HIGHEST RETAIL_COST
END