Union with same table

[Anonymous]

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

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!

[techvarun]

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

[Anonymous]

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.