Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
biggusdoggus
Contributor

Union with same table

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!

2 Replies
techvarun
Valued Contributor II

Re: Union with same table

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

biggusdoggus
Contributor

Re: Union with same table

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.