Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
jamietomkins
Partner - Contributor II
Partner - Contributor II

Finding difference between two sets of values in Macro

Hi,

I have for loop in a macro that works off of a set of values and I have the following two sets of values:

Set x= ActiveDocument.Fields("FIELD_1").GetSelectedValues

Set y= ActiveDocument.Fields("FIELD_2").GetPossibleValues

I want to get all the values that are selected in FIELD_1 and possible in FIELD_2, and do the for loop on those.

In a pseudo-code way, something like:

z=x intersect y

Does anyone know a way I could achieve this in VBScript?

 

Thanks,

 

Jamie

Labels (3)
4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

I'm not sure that I understand what do you want to do because getpossiblevalues from field_2 considered already the selections in field_1.

- Marcus

jamietomkins
Partner - Contributor II
Partner - Contributor II
Author

Say that GetSelectedValues from FIELD_1 is (A,B,C,D,E)

Say that GetPossibleValues from FIELD_2 is (C,D,E,F)

I want to catch the intersectional array of (C,D,E) to for loop on

marcus_sommer
MVP & Luminary
MVP & Luminary

Ok. I think I wouldn't do this matching within the vbs else I would try to use the native Qlik functions, for example with something like:

concat({< FIELD_1 = p(FIELD_2)>} distinct FIELD_1, ',')

This could happens within a variable which is then queried in the macro or maybe also with an evaluate() statement in the macro. Something in this way:

var = ActiveDocument.Evaluate("concat({< FIELD_1 = p(FIELD_2)>} distinct FIELD_1, ',')")
arr = split(var, ",")

for i = 1 to ubound(arr)
   ....
next

cwolf
Creator III
Creator III

sub InterSection
    set val1=ActiveDocument.Fields("F1").GetSelectedValues
    set val2=ActiveDocument.Fields("F2").GetPossibleValues
    s=""
    if val1.count > 0 and val2.count > 0 then
        set dic1=CreateObject("Scripting.Dictionary")
        set intersec=CreateObject("Scripting.Dictionary")
        
        for i = 0 to val1.count-1
            dic1.Add val1.Item(i).Text,1
        next        

        for i = 0 to val2.count-1
            if dic1.Exists(val2.Item(i).Text) then
                intersec.Add val2.Item(i).Text,1
            end if
        next        
        
        if intersec.count>0 then
            values=intersec.Keys()
            for i=lbound(values) to ubound(values)
                s = s & values(i) & " "
            next
        else
            s ="No values!"
        end if
    else
            s ="No values!"
    end if
    MsgBox(s)
end sub

- Christian