Orphan Records in Qliksense

[benwashburne]

stalwar1‌

Hey again Sunny,

This is more of a general question for Qliksense:

Do you know of a workaround for dealing with measures against orphan records in Qliksense? For example, I have two tables that I have brought in and they are joined on a primary key. One table has over 13,000 records and the other has only 150. As you could imagine, this causes Qlik to generate orphan records (which appear as hyphens if you're looking at a table visualization) for thousands of records across multiple columns but obviously not the primary key column. 

Now, if I create a KPI that does a Count(Primary_Key), it only counts the 150 records from the smaller table.Do you know why?

Furthermore, is there a way to give those orphan records a meaningful value like 'No match' or some other string value?

The best workaround I have right now is to merge the tables in Qlik, download them as an excel sheet, plot the null/orphan records and then re-upload that sheet as a new, merged table.

Thanks!

Ben

[sunny_talwar]

Are you saying that one table have 13,000 different values for primary_key and other table have 150 different values for primary_key? Also, read this with regards to counting primary keys

Count or Count distinct?

[YoussefBelloum]

Also,

if you right join on the "13k records table" the line of the second table OR you left join on the "150 records table" the lines of the "15K records tables", you will have only 150 lines on the key.

so verify the type of the join you're using

[benwashburne]

Hi Sunny,

Yes, apologies for the late response. I have also posted another question in regards to handling null orphan values.

Essentially, I often work across tables where one table will have 100,000+ rows with a unique key and I will have another table with different data with that unique key available as well except this table only has about 10,000 rows. The result of a qlik join on these keys is 100,000s of rows with null orphan values because there was simply no keys to match after the 10,000 from the smaller table were joined. I need a way to handle those orphan null values...Does this make sense?

[marcus_sommer · Accepted solution]

This behaviour doesn't bother in the most views respectively it has some advantages against other approaches but if you really want to avoid it you must "fill" these missing values.

One way might be your already made logic whereby it could be done directly in Qlik without the need of a dertour in Excel - just by (left) joining the big table to the small table and reloading this table again and checking the values of NULL and replacing them like you want (the same could be done with a mapping-approach) or you used NULL variables to fill the values.

Another way might be to reduce the big table on the size of the small table or to fill the small table with their missing values in regard to the big table - both could be reached with a where exists() clause.

But like above stated it's usually not necessary to do to create valid and sensible views and I think it there will be rather more disadvantages than benefits so that you should have a good reason of doing it.

More background to NULL's could you find here: NULL handling in QlikView.

- Marcus

[benwashburne]

Thanks, Marcus.

For now, an outer join followed by a null map load is working for me.

Best,

Ben