Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
MichalS
Contributor
Contributor

Filtering out "-" - no association

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

Labels (3)
6 Replies
jayanttibhe
Creator III
Creator III

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

try is missing function

Channa
MichalS
Contributor
Contributor
Author

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

Regards

jaws1990
Contributor II
Contributor II

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

Hi @jaws1990 

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

Filtering-Out-01.png

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.

Filtering-Out-02.png

hth

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
sudha3295
Contributor II
Contributor II

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

sudha3295_1-1650880448142.png