Newbie Trying to use and mode

[#1]

Hi,

I am very new to Qlikview so hopefully not going to make too much of a fool of myself with this question.

I am trying to perform basket level analysis on retail store sales. All works fine and looks great except that I would like to be able to look at sales records where the customer has bought the same two or more products. for instance - we sell lots of cans of coke and lots of mars bars, but lets look at the baskets where they buy both of these products, what else do they buy?

Multiple selections defaults to OR, I have found and tried the help instructions to change this to AND using the And mode switch, however regardless of what I try I cant get this tick box to be available, its always greyed out.

I am sure this is a commonish requiriment, so what am I doing wrong?

Thanks in advance.

Mark

[swuehl]

Hi Mark,

there are some pretty strict requirements for the use of and-mode in list boxes.

From the help:

And-Tables

A field cannot always be set to logical and mode. The reason for this is that the and alternative is logically meaningful only if the concerned field is linked to only one other field.

The AND-mode option is only allowed under very strict conditions, imposed by the theory behind the QlikView logic. In order to be used in AND-mode, the following criteria must be fulfilled:

• the field must only exist in one logical table,
• the field must be the second column of no more than two columns, and
• the table must not contain any duplicate records, and
• the table must be loaded using a distinct qualifier. If the table is loaded using a Select statement, you must use a preceding Load distinct *.

Do your setting fulfills all these requirement?

[#3]

Thanks for the swift response.

I have now got the process working on a simple test, loading two test fields from a spreadsheet, so can make the tick box available. However my live data is more complex and doesnt meet the strict rules above - I can manage everything except the no duplicates constriant, and that is legitimate duplication as uniqeness is based on four indexes and it appears I can only bring in two fields.

I feel there must be an alternative solution but cant spot anything in the user help.

Mark

[swuehl]

Hi Mark,

the restrictions (i.e. two fields only) are related to the table of which the and-mode field is coming from. You could link the first column to other tables, if that is from any help.

You could also create a unique field by using QlikViews functions in the load script, like concat() or autonumber,  for concatanating some fields and/or creating a unique ID.

Maybe you could post a little more details about your data model, if possible you might post also a small sample application here (upload available in advanced editor).

I've also just found that thread: http://community.qlik.com/message/127398#127398 , maybe that's of any help, too.

Stefan