Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
See attached example.
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;
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
PFA
Tanks