How to implement AND operator for values from same column

[siddharth_kulka]

Here we go-

Data looks like:

Sales:

Customers, Bikes, Sales

Mike,          A     , 10  

Mike,          B     , 10

Steve,         A     , 10

Laura,         B     , 10

Problem Statement: Display only those Customers who have bought both Bikes- A as well as B

Expected Result: Mike

How do I achieve this using:

1. Qlikview Backend (Scripting Only)

2. Qlikview Front End (UI Only)

3. Not in Qlikview , but In SQL while loading data.

Quick turnaround will be appreciated.

Thanks in advance, geeks!

[siddharth_kulka]

Clarification for Point 3.

Not in Qlikview- Meaning using SQL ONLY without any Qlikview functions. Still using Qlikview scripting.

[Anonymous · Accepted solution]

QV Scripting and SQL would be a Similiar approach just with different Syntax (See Below for example in QV since I'm more comfortable with it than SQL).  The UI you would use Set Analysis and the '*' Operator with the P() function.  There are a lot of examples of this in this forums just search for it.

Temp:

LOAD *

Where Count > 1;

LOAD Distinct

  Customers,

  Count(Customers) as Count

Group by Customers;

LOAD Customers INLINE [

    Customers, Bikes, Sales

    Mike,          A     , 10

    Mike,          B     , 10

    Steve,         A     , 10

    Laura,         B     , 10

]

Where Match(Bikes, 'A','B');

Table:

LOAD * INLINE [

    Customers, Bikes, Sales

    Mike,          A     , 10

    Mike,          B     , 10

    Steve,         A     , 10

    Laura,         B     , 10

]

Where Exists(Customers);

Drop Table Temp;

[siddharth_kulka]

Hi Michael,

Thats nice and does work!

On the foundation of your logic, I built up something which in my opinion would keep things simpler.

Script:

LOAD * INLINE [

    Customers, Bikes,Sales

    Mike, A,10

    Mike, B,10

    Steve, A,10

    Laura, B,10

]WHERE Match(Bikes, 'A','B');

UI Expression:

if(aggr(count(Bikes),Customers)>1,sum(Sales))

P.S: This is assuming the data is aggregated in the first place. i.e- Cannot have 2 records with Mike, A, 10 to start with.

Cheers,

Sid

[Anonymous]

Very slick solution.

Try the below equation in the UI with no script changes.

SUM({<Customers = p({<Bikes = {'A'}>*<Bikes={'B'}>}Customers)>}Sales)