Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: We have made updates to resolve a Page Not Found error. If you still get this error, please let us know in the Community Corner.
Highlighted
Partner
Partner

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
MVP & Luminary
MVP & Luminary

Re: Finding difference between two sets of values in Macro

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

Partner
Partner

Re: Finding difference between two sets of values in Macro

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

MVP & Luminary
MVP & Luminary

Re: Finding difference between two sets of values in Macro

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

chrwolf64
Contributor III

Re: Finding difference between two sets of values in Macro

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