5 Replies Latest reply: Sep 5, 2018 7:15 PM by Samuel Washburne RSS

    Orphan Records in Qliksense

    Samuel Washburne

      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

        • Re: Orphan Records in Qliksense
          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?

            • Re: Orphan Records in Qliksense
              Samuel Washburne

              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?

                • Re: Orphan Records in Qliksense
                  Marcus Sommer

                  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

              • Re: Orphan Records in Qliksense
                youssef belloum

                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