NEW to QV and Evaluating - Question on Selecting fields

[#1]

All,

I accidently posed the same question in QV corner and not able to delete.

Now posting in the right place.

I am very new to QlikView . We are in the process of evaluating QV.  I have downloaded the personal edition and was playing with it, showing demos to our executives and so forth.

One key thing came up in our discussion was doing an "OR" selection between two fileds.

EX.  If I want to see orders were “created on 1/1/2011”  OR “Product_Code = 10002”

By default (according to my very little understanding with QV) if I select two fields they are being joined using “AND”  and the above selection will retrieve all the order for 10002 on 1/1/2011.

Another example.

If I want to see orders that were generated for“Product_Code = 10002” on 1/1/2011  also for “Product_Code = 80009” on 4/1/2010

(Product_Code = 10002  AND ORD_DT = 1/1/2011")   OR   (Product_Code = 80009   AND ORD_DT = 4/1/2010)

is this possible?

Thanks,

AP

[johnw]

I assume you want a user to be able to select such values on the fly, and not hardcode specific values in the application?

I'd say possible but not simple, and a bit clunky to use.

My first thought would be that you'd let your users select the first set of data.  Have them hit a button that executes an action to create/update a bookmark for those selections.  Now have them make the second set of selections.  In whatever charts needed the OR functionality, use set analysis to union the two sets of data together.  Like if you had a chart that was summing sales by customer, it would normally have an expression like this:

sum(Sales)

For the OR function, it would need to do something like this:

sum({BM01}+{$} Sales)

That tells it to take the union of the bookmarked set (which, for the sake of argument, we've identified as BM01) and the currently-selected set {$}, and then sum sales for that.  It's not an OR across the entire document, but it is an OR in whatever charts you want.

I also have a few examples I put together for smaller tables and smaller number of fields that build an "Or Table" in the data model itself.  That would work seamlessly for the user, BUT at least in my examples requires you to designate whether each field is going to work in OR or AND mode.  I'm also pretty confident that for any reasonably-large data set, the "Or Table" would be too large to be practical.  I could post an example, but you can't read them in with personal edition.  I could post the script that creates it, but it wouldn't mean anything to you yet.  It's nothing too complicated, but there's quite a bit of it.

I suppose the details hardly matter when you're just evaluating the product.  Just understand that it won't be as simple to build or easy to use as your users might like.

Unless someone can think of a better way? 

I should also mention that questions about doing OR are very rare on the forum.  It would seem that most QlikView users never ask for this feature in practice.  It's one of those questions that occurs to people up front, "hey, what if I wanted to do this?", but then they never actually want to do this.  Your users could be the exception, of course.  I have examples on my hard drive because these questions DO come up, even if they're rare.

[#3]

Thanks John!

We advertise certain products thrucertain channel and would like to measure the sales effectiveness

ex .

Product A & B were advertisedin JAN-09

Product C & D were in JAN-10

Product E&F were in JAN-11

Right now what I do is write a SQL with a the followingcriteria

(

(Product  in (A, B) AND Month =  ‘JAN-09’)

OR

(Product  in (C, D) AND Month =  ‘JAN-10’)

OR

(Product  in (E, F) AND Month =  ‘JAN-11’)

)

[johnw]

Well, if it's acceptable to hardcode as you would in SQL, you could do something like this in any chart where you wanted to apply that logic. 

sum({<Product={'A','B'},Month={'JAN-09'}>
    +<Product={'C','D'},Month={'JAN-10'}>
    +<Product={'E','F'},Month={'JAN-11'}>} Sales)

You could also make the condition a variable so that you don't have to repeat it if you need it in several places.

LET Flag = {<Product={'A','B'},Month={'JAN-09'}>
           +<Product={'C','D'},Month={'JAN-10'}>
           +<Product={'E','F'},Month={'JAN-11'}>}

sum($(Flag) Sales)

For that matter, as long as you're hardcoding, you could do something like this:

if(match(Product,'A','B') and Month='JAN-09'
or match(Product,'C','D') and Month='JAN-10'
or match(Product,'E','F') and Month='JAN-11',Sales) as FlaggedSales

sum(FlaggedSales)

Again, I suppose the details aren't important.  But yes, if you're hardcoding, there are multiple ways to flag those specific sales.

[rwunderlich · Accepted solution]

In reading this thread I image you will most likely wind up linking a "campaignId" to both the Product and Date fields.  Then the selection or dimension is campaignId.

It's worth pointing out that adhoc search expressions -- which can be entered by users or saved in bookmarks -- can use OR logic. For example, if you had an OrderId listbox or dropdown the user could type

=(Product_Code = 10002  AND ORD_DT = 1/1/2011")   OR   (Product_Code = 80009   AND ORD_DT = 4/1/2010)

and that would filter the set to match the criteria.

-Rob

[johnw]

I think Rob's nailed it.  Since the selection in a list box is an OR, all you need to do is link the sales campain ID list box to the product and month fields.  Select those three campaigns, and you get the complicated and/or logic you were asking for.  That seems like the right answer to me, and not any of mine.

Campaign, Product, Month
1, A, JAN-09
1, B, JAN-09
2, C, JAN-10
2, D, JAN-10
3, E, JAN-11
3, F, JAN-11

[#7]

Thank you Guys(rater I call you Gurus )!

During one of the sales call with QV ,they mentioned that QV community is very helpful. Did not realize that there would be gurus in community and are willing to help! Thanks again.

As I mentioned we are evaluating and QV looks very promising!

BTW the following expression has to be in the search box of the list box right?

=(Product_Code = 10002  AND ORD_DT = 1/1/2011")   OR   (Product_Code = 80009   AND ORD_DT = 4/1/2010)

I will try with an excel data and will update you .(Hopefully it will work in personal edition)

[rwunderlich]

BTW the following expression has to be in the search box of the list box right?

=(Product_Code = 10002  AND ORD_DT = 1/1/2011")   OR   (Product_Code = 80009   AND ORD_DT = 4/1/2010)

Yes. if a user wants  to search using adhoc expression.

[#9]

Rob,Thanks for the reply!

For some reason the adhoc expression does not work.

First I tried the one you had mentioned. Then realized I have a date format override so used the below expression

=(PRODUCT_CODE =10001 AND ORD_DT = 40179) OR (PRODUCT_CODE =10002 AND ORD_DT = 40210)

40179 -> JAN-2010

40210 -> Feb-2010

Which didn't work either.

I have another field CHANNEL with values TV, INET, ADD

I tried again with the below expression and that didn't work

=(PRODUCT_CODE =10001 AND CHANNEL=TV) OR (PRODUCT_CODE =10002 AND CHANNEL=TV)

The only thing seems to be working is

10001 OR 10002

10001 AND 10002

everything I tried was in the search expression for "PRODUCT_CODE"

Truly appreciate your help!

Update.

If I do the following under CHANNEL search it works

=(CHANNEL ='TV' OR CHANNEL = 'ADD') , but can't combine anyother fields

[rwunderlich]

The search expressions can be a tricky to get working. Hard to say what might be wrong without seeing a sample qvw.

-Rob