2 Replies Latest reply: Oct 24, 2017 6:21 PM by Richard Judkins RSS

    Union with same table

    Richard Judkins

      Hi

       

      So I have a table, with a relatively large number of columns. It contains sales figures.

       

      I've made an example below - this table is nowhere near as big or complex as my real one, but hopefully it will give you an idea.

       

      Some of the sales were done by a Business client themselves, some of them done by a Corporate client on behalf of a Business client (Referred by), and some of them bought directly by a Corporate client to then on-sell. When bought directly by a Corporate there is nothing entered in the Referred by field.

       

      Now, what I'm wanting is for Qlik to only pull records of sales made by Corporate clients, whether referred or bought directly. But no duplicates.

       

      In plain English:

      Select the Client Names which are in the Corporate Segment, and Referred By names, removing any duplicates

      So, using the below example, I want Qlik to only bring through 3 records - Grape United, Signal Limited and Parrot Inc. Along with what they sold, and the date they sold it.

       

      And, how to "name" the resulting data-set? Something like "Referring Customers".

       

       

      Client NameReferred ByClient SegmentProductDate
      ABC Ltd

      Grape United

      BusinessCar01/02/2017
      Grape UnitedCorporateCar01/03/2017
      Beefy TimesSignal LimitedBusinessBoat16/06/2017
      Soggy TrousersSignal LimitedBusinessTrailer01/02/2017
      Signal LimitedCorporateBoat31/03/2017
      Parrot IncCorporateBoat17/07/2017
      Elephant HardwareBusinessCar23/09/2017

      Beefy Times

      Signal LimitedBusinessBoat01/02/2017

       

      So I tried

       

      Select

      Product

      Date

      From Table1

       

      Select

      Client Name

      Where Client Segment = 'Corporate'

      From Table1

       

      Union

       

      Select

      Referred By

      From Table1

       

      But it doesn't work at all. Obviously the only duplicates I want to exclude are the customer names, not the products or dates.

      I'm a little lost, TBH, so any help greatly appreciated please!

        • Re: Union with same table
          Varun Prakash Paulraj
          Test:
          LOAD * INLINE [
              Client Name, Referred By, Client Segment, Product, Date
              ABC Ltd, 
              Grape United
              Business, Car, 01/02/2017
              Grape United, , Corporate, Car, 01/03/2017
              Beefy Times, Signal Limited, Business, Boat, 16/06/2017
              Soggy Trousers, Signal Limited, Business, Trailer, 01/02/2017
              Signal Limited, , Corporate, Boat, 31/03/2017
              Parrot Inc, , Corporate, Boat, 17/07/2017
              Elephant Hardware, , Business, Car, 23/09/2017
              Beefy Times
              Signal Limited, Business, Boat, 01/02/2017
          ];
          
          
          noconcatenate LOAD * Resident Test where [Client Segment]= 'Corporate';
          DROP Table Test;
          
          
          

           

           

          Not clear about you final expected result. Do you want something like below? then use the above script

           

          sample.PNG

            • Re: Union with same table
              Richard Judkins

              Thanks Varun.

               

              I should have clarified sorry - it's a dynamic table. It gets updated remotely every day (we use Qlik Sense Server).

               

              So the script can't be that specific.

               

              As far as the expected result, it will be a visualisation that show the number of distinct "Referring Customers" for any defined time period.

              So in my example (for example), it would be 2 for February, 2 for March, and for Year to Date 3. There is some other script being used to define dates etc.