topic Re: Sorting GetPossibleValues in Macro in QlikView

Sorting GetPossibleValues in Macro

wnorwood — Tue, 01 May 2012 18:59:29 GMT

Hello,

I am using QV10. I am building a straight table chart dynamically. The dimensions and the expressions are sourced from tables. I make a selection from a list box and the macro finds the field values and field names from the table for the selected value. The table is sorted in the order that the fields should appear in the chart.

At first it seemed to be working perfectly, but sometimes the field values and field names are in different order so that the field gets the wrong name and the columns are in the wrong order. The field names seems to be sorting correctly, it is the field values that are off. Below is the snipit of code that it performing this action. Any help is appreciated!!

'add dimensions
set dimSelection = ActiveDocument.fields("Dimension").GetPossibleValues
set dimName = ActiveDocument.fields("DimensionName").GetPossibleValues

for i=0 to dimSelection.Count - 1

msgbox(dimSelection.Item(i).text)
msgbox(dimName.Item(i).text)
chart.AddDimension dimSelection.Item(i).text

'rename an expression label
set p = chart.GetProperties
set dims = p.Dimensions(i)
dims.Title.v = dimName.Item(i).Text
dims.LabelAdjust = 1 'center
chart.SetProperties p
'SET p=Nothing

next

Re: Sorting GetPossibleValues in Macro

yavoro — Thu, 21 Jun 2012 09:26:15 GMT

Hi,

I have similar problem to solve.

I want to GetPossibleValues in alphabetical order. But as far as i'm aware GetPossibleValue uses the load order of the field.

So if you make sure you load the values in that field in the order you want that might help.

I'm still looking for the alphabetical solution though.

Regards

Re: Sorting GetPossibleValues in Macro

Tue, 09 Apr 2013 14:27:40 GMT

Hi,

the problem is that your Diemnsion and Name is not stored with a connection to eachother.

If you load them into one field and then split them it would work.

LOAD

Dimension & '|' & DimensionName AS Dimensionfield,

ORDER BY

Dimension, DimensionName

And then split them in then macro

for i=0 to dimSelection.Count - 1

chart.AddDimension SUBFIELD(dimSelection.Item(i).text, '|', 1)

'rename an expression label

set p = chart.GetProperties

set dims = p.Dimensions(i)

dims.Title.v = SUBFIELD(dimName.Item(i).Text, '|', 2)

dims.LabelAdjust = 1 'center

chart.SetProperties p

'SET p=Nothing

Cheers

Re: Sorting GetPossibleValues in Macro

marcus_sommer — Tue, 09 Apr 2013 16:04:18 GMT

Alternative to read a field you could put the dimension + name in a tablebox - see APIGuide.qvw for examples like this:

set table = ActiveDocument.GetSheetObject( "CH01" )

CellRect = ActiveDocument.GetApplication().GetEmptyRect()

CellRect.Top = 0

CellRect.Left = 0

CellRect.Width = table.GetColumnCount

CellRect.Height = table.GetRowCount

set CellMatrix = table.GetCells( CellRect )

for RowIter=CellRect.Top to CellRect.Height-1

for ColIter=CellRect.Left to CellRect.Width-1

msgbox(CellMatrix(RowIter)(ColIter).Text)

For sorting an array see examples like this (BubbleSort):

http://www.robvanderwoude.com/vbstech_data_array.php

- Marcus