topic Re: Dynamic Sorting in the Script in QlikView

Dynamic Sorting in the Script

Fri, 30 Oct 2015 23:50:54 GMT

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?

Re: Dynamic Sorting in the Script

roger_stone — Sat, 31 Oct 2015 17:35:54 GMT

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.

Re: Dynamic Sorting in the Script

petter — Sat, 31 Oct 2015 17:55:50 GMT

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:

Re: Dynamic Sorting in the Script

Mon, 02 Nov 2015 20:47:58 GMT

Works like a charm. Thanks

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

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

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