- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Clarification for Point 3.
Not in Qlikview- Meaning using SQL ONLY without any Qlikview functions. Still using Qlikview scripting.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Very slick solution.
Try the below equation in the UI with no script changes.
SUM({<Customers = p({<Bikes = {'A'}>*<Bikes={'B'}>}Customers)>}Sales)