7 Replies Latest reply: Mar 1, 2017 2:10 AM by Rahul Pawar RSS

    Match

    Jia Sheng Loh

      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

        • Re: Match
          Jia Sheng Loh

          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?

          • Re: Match
            Wei Jiet Chiu

            is this what you want?

              • Re: Match
                Jia Sheng Loh

                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).

                  • Re: Match
                    Wei Jiet Chiu

                    Hi Jia,

                    Not sure the way i did correct or not.

                      • Re: Match
                        Jia Sheng Loh

                        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.

                          • Re: Match
                            Wei Jiet Chiu

                            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.

                            • Re: Match
                              Rahul Pawar

                              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