IF ()

[Anonymous]

Temp_Table
ID
COUNT
LOCATION
SIGR

I'm trying to create a band range from field sigr where count field is maximum count. Below is my logical syntax

IF(SIGR <= 99999 AND COUNT = max(COUNT) ,'A',

IF( SIGR > 99999 AND  SIGR <= 49999 AND COUNT=max(Count),'B' )) as RangeField

Thanks

[MayilVahanan]

Hi

Try like this

Temp_Table:

Load * from Temp_Table;

Left join(Temp_Table)

Load ID, Max(Count) as CountTemp resident Temp_Table group by ID;

Load *,

IF(SIGR <= 99999 AND COUNT = CountTemp  ,'A',

IF( SIGR > 99999 AND  SIGR <= 49999 AND COUNT=CountTemp ,'B' )) as RangeField

resident Temp_Table;

Drop table Temp_Table;

Drop field CountTemp;

[Anonymous]

Header 1	Header 2	Header 3	Header 4	Header 5
ID	COUNT	LOCATION	SIGR	VALUE
AA23	01	UK	10000	45000
AA23	02	UK	15000	56000
AA23	03	UK	500	65000

By your model (scripts);

The last row will be selected since it has the Max count, the range will be A since it less then 99999 and it's corresponding value that is 65000, unfortunately that is not the desired result.

I had wanted the summation for all the value that is 45000 + 56000 + 65000 this particular range field. Tried to SUM the value field as way but didnt get it.

Thanks