Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Minimum function through scripting?

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

PlaceWarehouseSourceFreight1Freight2Sum (Freight1, Freight2)
P1WH1S110010110
P1WH1S220020220
P1WH2S120010210
P2WH2S130020320
P2WH2S240010410

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.

PlaceWarehouseSourceFreight1Freight2Sum (Freight1, Freight2)Classification
P1WH1S110010110L1
P1WH1S220020220L3
P1WH2S120010210L2
P2WH2S130020320L1
P2WH2S240010410L2

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

Kindly help.

Thanks in advance.

5 Replies

Re: Minimum function through scripting?

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
Honored Contributor II

Re: Minimum function through scripting?

Please find attached

HTH

sushil

Not applicable

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.

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
Honored Contributor II

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

Not applicable

Re: Minimum function through scripting?

Thanks Sushil.

Community Browser