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

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:

TransactionBidderContact Number
10001AA123456789
10001AA123456789
10001BB987654321
10002BB987654321
10003CC114477888
10004DD125588779
10004DD125588779
10004AA

123456789

1005EE

555599995

1005EE

555599995

1005EE

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:

TransactionContact Person

Contact Number

1001AA123456789
1001AA123456789
1004DD125588779
1004DD125588779
1005EE555599995
1005EE555599995
1005EE555599995

Regards,

Jia

7 Replies
Anonymous
Not applicable
Author

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?

weijietc
Partner - Contributor III
Partner - Contributor III

is this what you want?

Anonymous
Not applicable
Author

Hi Jiet Chiu,

my result table will be this:

TransactionContact Person

Contact Number

1001AA123456789
1001AA123456789
1004DD125588779
1004DD125588779
1005EE555599995
1005EE555599995
1005EE555599995

And my KPI will show 3 (cases of bidder bidding a transaction multiple times).

weijietc
Partner - Contributor III
Partner - Contributor III

Hi Jia,

Not sure the way i did correct or not.

Anonymous
Not applicable
Author

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.

weijietc
Partner - Contributor III
Partner - Contributor III

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.

rahulpawarb
Specialist III
Specialist III

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