Dynamic Sorting in the Script

[#1]

Hello All,

I'm don't know if QlikView can solve this problem, but any guidance would be great.

I have a table that looks like this:

field1        field2

AAA	ZZZ
AAA	YYY
AAA	XXX
BBB	WWW
BBB	VVV
BBB	UUU
BBB	TTT
CCC	SSS
CCC	RRR
CCC	QQQ
CCC	PPP
CCC	OOO
CCC	NNN
CCC	MMM
CCC	LLL
CCC	KKK

I need logic that dynamically ranks so that it looks like this:

field1        field2          rank

AAA	ZZZ	1
AAA	YYY	2
AAA	XXX	3
BBB	WWW	1
BBB	VVV	2
BBB	UUU	3
BBB	TTT	4
CCC	SSS	1
CCC	RRR	2
CCC	QQQ	3
CCC	PPP	4
CCC	OOO	5
CCC	NNN	6
CCC	MMM	7
CCC	LLL	8
CCC	KKK	9

I can guarantee sort order of field1 and field2.  The business needs dictate that this must happen in the script.

Any suggestions?

[roger_stone]

You'll need to use Peek() in your script.

Something along the lines of

IF (Peek('field1')=[field1], Peek('fieldctr')+1, 1) as fieldctr

fieldctr gets reset to 1 every time field1 changes.

Hope that helps.

[petter · Accepted solution]

If the field2 is already sorted in the rank they should be as it seems to be the case here - this script should do:

The AutoNumber with the second parameter given to separate the groups of field1 will count a sequence independently resulting in the Rank you seem to need...

Resulting in this:

[#4]

Works like a charm.  Thanks