Filtering out "-" - no association

[MichalS]

Hello,

Maybe anyone of you had similar problem.

I have two tables:

Table 1 contains: Book Name, Account, Amount (let's say there's two hundred books here)

Table 2 contains: Book Name, Book Owner (let's say there's 10 books here)

I associated two table by book name. Now, important thing for me is to be able to sort out those books that have no Book Owner and checked what accounts are there. So I created a table in Qlik and tried to filter out clicking on the "-" symbol in the table. And as it works great for the place where the book owner's name is displayed it seem not to work  for "-". This field is also greyed out in the table. Does that mean that  I cannot filter out those fields for which there's no association?

To make it clearer, if I did vlookup in table 1 returning there Book Owner from table 2 I would need to filter out #N/A- meaning rows where Book Owner was not found.

Can you please advise whether this is possible at all or do I have to make vlookup before I upload data to Qlik?

Thank you

Regards

Michal

[jayanttibhe]

Hi- 

"-"  meaning "NULL" book owner and by default Qlik cannot filter on Nulls (which is absolutely correct). You need to do following. 

1. Either you use "Mapping Load" on second table and place "NA" OR "-"  [note - "-" in this case is Character] to populate the non matching / Null Book names.

2. Left join second table to first table then use resident load on resultant table to populate the Null Book names in the second table

[Channa]

try is missing function

[MichalS]

Hi Channa, can you please elaborate what you had in mind?
Thank you

Regards

[jaws1990]

This is a real pain in the behind for me not being able to filter on non associated records or use them in set analysis.

[ArnadoSandoval]

Hi @jaws1990 

You can make them 'visible' through your load script, let me explain:

The table association works well in Qlik, but if we need to track fields (association fields) missing associations, we can implement an ApplyMap, returning 1 when there is an association or 0 end there is none; the script below does just that on the example given by the OP.

Owners:
LOAD
    "Book Name",
    "Book Owner"
FROM [lib://SourceData/Books.xlsx]
(ooxml, embedded labels, table is Owners);

Map_Owner:
Mapping Load
	"Book Name",
    1
Resident Owners;

NoConcatenate

Books:
LOAD
    "Book Name",
    ApplyMap('Map_Owner', "Book Name", 0) As Associated_Owner,
    "author",
    year_written,
    edition,
    price
FROM [lib://SourceData/Books.xlsx]
(ooxml, embedded labels, table is Books);

This new column, Associated_Owner tells us with a 0-1 flag when we are missing an association.

hth

[sudha3295]

In the UI write your the condition, using fields from both table. in your case use  Book Name & Book Owner fields, then you will be able to replace a text for no association with making change to script/data model.

Eg:

For below script 

TabA:
load * inline [
A, B
1, A
2, B
];

TabB:
Load * inline [
B,C
B,zzz
];

Here is the UI That replace the no association with text "Unknown" by expression :

=IF(len(C)>0 and len(B)>0,C,'Unknown')   or   use   =IF( isnull(C) and not isnull(B),'Unknown',[C])