topic Get a macro to use select excluded in QlikView

Get a macro to use select excluded

Tue, 09 Nov 2010 15:14:36 GMT

Hello,

I'm using QV version 8.2.

I'm trying to create an application where we can select which customers to send newsletters to.

Part of this selection is to check against a number of products the customer must have bought (at least one).

There is also a list of products the customer can not have bought already (normally what the offer is about).

I have created a macro that solves the uploading of the product list.

The code for the macro is as follows:

sub multySelection_Web_ArtNr_not_bought
set v = ActiveDocument.GetVariable("vinput_artno_not_bought")
vinput_artno = v.GetContent.String
strlength = Len(vinput_artno_not_bought)
set f = ActiveDocument.Fields("Web_ProductNumber")
set fv = f.GetNoValues

for i = 0 to strlength +1
commaF2 = InStrRev(vinput_artno,",")
inpar = mid(vinput_artno,commaF2 + 1,len(vinput_artno))
if not IsNumeric(inpar) then
msgbox("Please use correct format")
exit for

end if
fv.Add
fv(i).Number = inpar
fv(i).IsNumeric = true
if not commaF2 = "0" then
vinput_artno = left(vinput_artno,commaF2-1)
else

exit for

end if

f.SelectValues fv

end sub

My problem is how do I get QV to run the productlist against what a customer has not bought?

In version 8.5 or later I assume I would be able to use set analysis but is there another way that will work with 8.2?

Martin

Get a macro to use select excluded

Tue, 09 Nov 2010 17:02:47 GMT

Martin,

Not quite sure what you are trying to do here, but the attached document shows how to find all those parts that customers havent bought.

Its effective if not efficient but as it creates a matrix of all customers/parts then I wouldnt recommend it where you have millions of customers and millions of parts!

Regards,

Gordon

Get a macro to use select excluded

Wed, 10 Nov 2010 16:12:38 GMT

Hello,

Thanks for you input.

My problem is that we do have millions of customers and millions of parts.

First of all we have all our orders already in the application that amount to a couple of millions.

Then our marketing department comes with a request that goes something like.

Give me a list of email addresses where the customer has purchased one ot the following products (a list with in extreme cases 10 000 articlenumbers) last year that has not purchased one of the following ( another list with in extreme cases 10 000 articles).

The reason I have created the macro is to get the 2 lists into the application.
But I have not found a way to get QV to understand that the second list should be applied on all orderrows on the orders from result of the first list.

Any ideás?

By the way, how do you upload files? I could upload my application to show you.

Martin

Get a macro to use select excluded

Wed, 10 Nov 2010 16:47:36 GMT

I read this earlier in teh QV Ref manual which intrigues me and may help:

sum( {$<Customer = E({1<Product={'Shoe'}>})>} Sales)
returns the sales for current selection, but only those customers
that never bought the product 'Shoe'. The element
function E( ) here returns the list of excluded customers;
those that are excluded by the selection 'Shoe' in the field
Product.

Regards,

Gordon

PS You can upload using the 'Options' tab when replying to a post