topic How do I get all values of a field through API in QlikView

How do I get all values of a field through API

Thu, 23 Sep 2010 16:20:27 GMT

Greetings!

I have trouble finding the correct API for getting all values of field. There are bunch of methids returning different varietes of selected, excluded or optional values. But I can't find any method that actually returns all values of a field, no matter their current state.

This is what I am trying to do. My problem with this code is that GetPossibleValues only returns the currently selectable values of the field. I want to get all values.

Public Overloads Sub FilterField(ByVal fieldname As String, ByVal fieldValues As ArrayList, Optional ByVal ClearPreviousSelection As Boolean = True)
 Dim p As QlikView.Field = App.ActiveDocument.GetField(fieldname)
 If ClearPreviousSelection Then p.Clear()
 Dim a = p.GetPossibleValues(p.GetCardinal)
 For i = a.Count - 1 To 0 Step -1
 Debug.Print(i.ToString & " - " & a(i).Text)
 If Not fieldValues.Contains(a(i).Text) Then
 a.RemoveAt(i)
 End If
 Next
 p.SelectValues(a)
 End Sub

I could also use second call like this to get the mutually exclusive values. But the .Add method of thge object returned is a com object and don't take any parameters. So I cant merge a and b into one array.
Dim b = p.GetExcludedValues(p.GetCardinal)

So what method should I call?

/Peter

How do I get all values of a field through API

Thu, 23 Sep 2010 16:31:38 GMT

I'm not sure there is a method to Get All Values. It seems that there should be.

A workaround may be to create a QlikView variable and give it the value:

Concat({1} DISTINCT FieldName, ',')

Then in your macro:

valList = ActiveDocument.Variable("vVar").GetContent.String
valAry = Split(valList, ",")

That should give you an array with all of the values of a field whether they are selected or not. I'm not sure how well that is going to work in your routine though.

How do I get all values of a field through API

Thu, 23 Sep 2010 17:01:35 GMT

It could be something like this

How do I get all values of a field through API

Thu, 23 Sep 2010 17:18:42 GMT

I strongly advice *not* to use the fields method: they are *very* slow. If you want to get all values, the fastest (which fortunately turns out to be the easiest too) is to Evaluate an expression which concatenate all your values with a proper set analysis which does not take the selection into account.