topic Re: How to implement AND operator for values from same column in QlikView

How to implement AND operator for values from same column

siddharth_kulka — Sat, 28 Mar 2015 22:50:30 GMT

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!

Re: How to implement AND operator for values from same column

siddharth_kulka — Sat, 28 Mar 2015 23:03:36 GMT

Clarification for Point 3.

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

Re: How to implement AND operator for values from same column

Anonymous — Sun, 29 Mar 2015 00:06:15 GMT

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;

Re: How to implement AND operator for values from same column

siddharth_kulka — Sun, 29 Mar 2015 01:51:26 GMT

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

Re: How to implement AND operator for values from same column

Anonymous — Sun, 29 Mar 2015 03:53:16 GMT

Very slick solution.

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

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