topic Finding difference between two sets of values in Macro in QlikView

Finding difference between two sets of values in Macro

Anonymous — Sat, 16 Nov 2024 04:55:58 GMT

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

Re: Finding difference between two sets of values in Macro

marcus_sommer — Wed, 19 Dec 2018 12:56:39 GMT

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

Re: Finding difference between two sets of values in Macro

Anonymous — Wed, 19 Dec 2018 13:13:16 GMT

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

Re: Finding difference between two sets of values in Macro

marcus_sommer — Wed, 19 Dec 2018 13:59:48 GMT

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

Re: Finding difference between two sets of values in Macro

cwolf — Thu, 20 Dec 2018 12:18:19 GMT

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