Minimum function through scripting?

[#1]

I have the following table after doing some work on the base data.

Place	Warehouse	Source	Freight1	Freight2	Sum (Freight1, Freight2)
P1	WH1	S1	100	10	110
P1	WH1	S2	200	20	220
P1	WH2	S1	200	10	210
P2	WH2	S1	300	20	320
P2	WH2	S2	400	10	410

I want to add one column "Classification", which would give

     "L1" if the Sum column for a given Place is the MINIMUM among the available options.

     "L2" if the Sum column for a given Place is the 2nd MINIMUM and so on.

I have showed the example for 2 Places.

Something like this.

Place	Warehouse	Source	Freight1	Freight2	Sum (Freight1, Freight2)	Classification
P1	WH1	S1	100	10	110	L1
P1	WH1	S2	200	20	220	L3
P1	WH2	S1	200	10	210	L2
P2	WH2	S1	300	20	320	L1
P2	WH2	S2	400	10	410	L2

I got the answer for doing this through expression, but I am looking for doing this through script.

Kindly help.

Thanks in advance.

[rwunderlich]

How about this way (sample QVW attached).

data:

LOAD *,

          Freight1 + Freight2 as TotalFreight

;

LOAD * INLINE [

Place          Warehouse          Source          Freight1          Freight2

P1          WH1          S1          100          10          110

P1          WH1          S2          200          20          220

P1          WH2          S1          200          10          210

P2          WH2          S1          300          20          320

P2          WH2          S2          400          10          410

] (delimiter is '\t')

;

data2:

LOAD

          *,

          'L' & AutoNumber(TotalFreight, Place) as Classification

RESIDENT data

order by TotalFreight

;

DROP TABLE data;          // Drop orginal table

-Rob

http://robwunderlich.com

[sushil353]

Please find attached

HTH

sushil

[#4]

Thanks a lot Rob and Sushil..

Both answers are of same logic

This works.

I checked the reference manual for AUTONUMBER. But I could not understand the logic behind its working.

Could you please help me with that?

Is there some way using functions like AGGR or MIN and GROUP BY?

Its for better understanding. Nothing else.

Many thanks

[sushil353]

hi,

Autonumber generate the counter based on the expression passed in the autonumber.. in this particular case we have passed the field name... that means for each field value it will generate a number...

now the second parameter is used to reset the counter based on the instance of the second parameter...

in this case we have to reset our counter for each Place type..

now at last we are using order by clause to put the right counter value from top to lower records..

Hope the explanation helped you to understood the functionality of autonumber.

Regards,

Sushil

[#6]

Thanks Sushil.