- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Match
Hi experts!
I have no idea what idea to use for title so I use Match.
The case is this:
A bidder could only bid for the same transaction only once.
Thus, I like to count the Transaction where the bidder bid more than once.
Example data:
Transaction | Bidder | Contact Number |
10001 | AA | 123456789 |
10001 | AA | 123456789 |
10001 | BB | 987654321 |
10002 | BB | 987654321 |
10003 | CC | 114477888 |
10004 | DD | 125588779 |
10004 | DD | 125588779 |
10004 | AA | 123456789 |
1005 | EE | 555599995 |
1005 | EE | 555599995 |
1005 | EE | 555599995 |
Given the above data,
The KPI count should be 3.
[Bidder]: AA bidded [Transaction]:1001, twice.
[Bidder]: DD bidded [Transaction]:1004, twice.
[Bidder]: EE bidded [Transaction]:1005,three times.
The Result table will look like this:
Transaction | Contact Person | Contact Number |
---|---|---|
1001 | AA | 123456789 |
1001 | AA | 123456789 |
1004 | DD | 125588779 |
1004 | DD | 125588779 |
1005 | EE | 555599995 |
1005 | EE | 555599995 |
1005 | EE | 555599995 |
Regards,
Jia
- Tags:
- match
- match field
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Update: It doesn't work. It only count distinct [Transaction] without referring to [Bidder]
Anyone could help me?
I think I found the it:
Count({<[Transaction] = {"=Count( [Transaction]&[Bidder])>1"}>} DISTINCT [Transaction]))
So what it does is
exp: for CC we'll only have 1003&CC : so 1
for EE: we'll have : 1005&EE+1005&EE+1005&EE : so 3 > 1 , we count it and so on;
Am I right?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
is this what you want?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jiet Chiu,
my result table will be this:
Transaction | Contact Person | Contact Number |
---|---|---|
1001 | AA | 123456789 |
1001 | AA | 123456789 |
1004 | DD | 125588779 |
1004 | DD | 125588779 |
1005 | EE | 555599995 |
1005 | EE | 555599995 |
1005 | EE | 555599995 |
And my KPI will show 3 (cases of bidder bidding a transaction multiple times).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jia,
Not sure the way i did correct or not.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jiet Chiu,
This is it, how do i do this?
And what should my KPI expression be to show 3 cases?
Btw, are you using qlikview? Not sure if will work with qliksense.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jia,
I added a recno to make the records unique and count the transaction & bidder in the edit script.
Can refer the attached test.qvw in previous reply
Display the recno by count of transaction & bidder records which are more than 1. (Hide it in qlikview straight table)
Yes, I am using qlikview.
For qliksense, I think you need to display the recno. Because qliksense can't hide column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Jia,
Trust that you are doing great!
Please refer below given sample script to get the bidder bidding a transaction multiple times:
Data:
LOAD Transaction,
Bidder,
[Contact Number],
AutoNumber(RowNo(), Transaction & Bidder) AS Id
FROM
[https://community.qlik.com/thread/251677?sr=stream&ru=230646]
(html, codepage is 1252, embedded labels, table is @1);
INNER JOIN
//Get the records where transaction counts is greather than 1
LOAD Bidder,
Transaction
Where TranCount > 1;
//Get the Transaction counts by Bidder & Transaction
LOAD Bidder,
Transaction,
Count(Transaction) AS TranCount
Resident Data
Group By Bidder, Transaction;
Also refer the attached application.
Hope this will be helpful.
Regards!
Rahul