5 Replies Latest reply: May 21, 2013 1:22 AM by Raghav Kumar

Minimum function through scripting?

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.

• Re: Minimum function through scripting?

data:

Freight1 + Freight2 as TotalFreight

;

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:

*,

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

RESIDENT data

order by TotalFreight

;

DROP TABLE data;          // Drop orginal table

-Rob

http://robwunderlich.com

• Re: Minimum function through scripting?

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.

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

Its for better understanding. Nothing else.

Many thanks

• Re: Minimum function through scripting?

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

• Re: Minimum function through scripting?

Thanks Sushil.