Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharth_kulka
Creator II
Creator II

How to implement AND operator for values from same column

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!

1 Solution

Accepted Solutions
Anonymous
Not applicable

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;

View solution in original post

4 Replies
siddharth_kulka
Creator II
Creator II
Author

Clarification for Point 3.

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

Anonymous
Not applicable

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
Creator II
Creator II
Author

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
Not applicable

Very slick solution.

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

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