Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
jiasheng
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

Tags (2)
7 Replies
jiasheng
Not applicable

Re: Match

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

Re: Match

is this what you want?

jiasheng
Not applicable

Re: Match

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

Re: Match

Hi Jia,

Not sure the way i did correct or not.

jiasheng
Not applicable

Re: Match

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

Re: Match

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

Re: Match

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