Make unrelated data related when no data exists

[hfkchristian]

Hi all

I hope someone can help me with this problem. The headline is probably a bit confusing, so I'll try to explain what I want to achieve.

I have a table which lists possible combinations of suppliers and places. An example:

Supplier	Place
Books Ltd	Norway
Books Ltd	Sweden
Games Ltd	Norway
Games Ltd	Denmark
Games Ltd	Finland
Toys Ltd

Now, when I loads this into QlikView, and make list boxes for suppliers and places, it works when there are data in the table. If I click on Books Ltd I get Norway and Sweden as possible values in the Places list box, which is fine.

The problem is the supplier Toys Ltd. It has no data in the place column, which means is should be related to all available places. When I click on Toys Ltd in my list box, I want ALL values in the places list box to stay as possible values. Unfortunately, they all end up as excluded values, and all my expressions in my charts show no data.

Is it possible to fix this? I load the data from an Excel sheet using subfield:

LOAD

Supplier,
  SubField(places,',')) As Place
FROM
excel_sheet.xls
(biff, embedded labels, table is Sheet1$);

In Excel it looks like this:

Supplier	Places
Books Ltd	Norway,Sweden
Games Ltd	Norway, Denmark, Finland
Toys Ltd

Any ideas what to do?

[Gysbert_Wassenaar]

See attached example.

[maxgro]

like this?

source:

buffer

LOAD Supplier,

    Places

FROM

[http://community.qlik.com/thread/156915]

(html, codepage is 1252, embedded labels, table is @2);

final:

NoConcatenate LOAD

  Supplier,

  SubField(Places,',') As Place

Resident source

where len(trim(Places))>0;

tmp:

load Supplier Resident source

where len(trim(Places))=0;

join (tmp) load Distinct Place Resident final;

Concatenate (final) load * Resident tmp;

DROP Table source, tmp;

[hfkchristian]

Thanks for your answers I hoped it would be possible to do this without creating lots of extra data, but I can live with this solution too.

Unfortunately, there is one more problem.

The list of countries in the Excel file is not complete. We also have another table which is already loaded into QlikView. This table lists all relevant countries with names and other data. It looks like this:

Location	Other data
Norway
Sweden
Denmark
Finland
Iceland
United Kingdom
Germany
France

I want all these locations to be related too, not only the locations in the Excel file (We can assume that all the locations in the Excel file will already be present in the table above). Would have been nice if someone could show me how to do this too

[maxgro · Accepted solution]

PFA

[hfkchristian]

Tanks