Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
is this what you want?
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).
Hi Jia,
Not sure the way i did correct or not.
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.
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.
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