topic Minimum function through scripting? in QlikView

Minimum function through scripting?

Fri, 17 May 2013 04:18:39 GMT

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.

Re: Minimum function through scripting?

rwunderlich — Fri, 17 May 2013 04:55:30 GMT

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

Re: Minimum function through scripting?

sushil353 — Fri, 17 May 2013 05:12:16 GMT

Please find attached

HTH

sushil

Re: Minimum function through scripting?

Fri, 17 May 2013 09:00:15 GMT

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

Re: Minimum function through scripting?

sushil353 — Fri, 17 May 2013 09:09:26 GMT

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?

Tue, 21 May 2013 05:22:04 GMT

Thanks Sushil.