5 Replies Latest reply: Jun 16, 2017 2:24 PM by Sunny Talwar RSS

    Qlik Sense - Set Analysis Intersection Issue - Duplicates and Table Order

    Justin McPheeters

      Hi everyone,

       

      I'm experiencing some odd behavior and am wondering if anyone can possibly explain why I'm getting the results that I am.

       

      Premise

      We have assets that come from various sources (called "Views" within the app). We match them based on certain criteria and then show the intersections via a Venn Diagram extension (image attached). This extension uses particular formulas to calculate the count of distinct assets in each section/intersection. Then, we have an Audittable that lists out, by asset, which fields have discrepancies between the original sources. Lastly, there are additional fields that I need to add to the main data table, so I load the entire table, add the needed fields, delete the original table, and then rename the new table.

       

      I've simplified the dataset and just provided the relevant data/script in the attached QVF file. I've duplicated the data and script for the four scenarios below as well as the visualizations that display the needed values. The KPIs are exactly the same - only varying to account for the various datasets.

       

      Scenario 1 - No Duplicates in Audit Table

      After narrowing down the issue to only occuring when there were duplicates in the Audit table, I created a dataset that did not have duplicates.

      Results: The KPIs are correct, but the scenario is unlikely. There are almost always duplicate AssetID values in the Audit table because there are multiple discrepancies in the source datasets.


      Scenario 2 - Duplicates in Audit

      Scenario 2 is exactly the same as Scenario 1, but there is a duplicate AssetID in the Audit table rather than two distinct values.

      Results: The KPIs are incorrect. They only display results for AssetIDs in the Audit table, not the entire dataset.


      Scenario 3 - Duplicates in Audit - Reload Main Data Table after Audit

      Scenario 3 is exactly the same as Scenario 2, but I switched the code segments to reload the main data table before the Audit table was loaded.

      Results: The KPIs are correct. I've switched my code to follow this scenario, but I would like to understand why it works when Scenario 2 doesn't.


      Scenario 4 - Duplicates in Audit - All AssetIDs in Audit Table

      Scenario 4 is exactly the same as Scenario 2, but I added all AssetIDs to the Audit table.

      Results: The KPIs are correct. This scenario is okay, but I'd prefer to just have the applicable AssetIDs in the Audit table to save on system resources.


      Overall, I'm just trying to find an explanation as to why the intersections for Scenario 2 don't work while they do for the other scenarios. It seems to have something to do with duplicates and/or table numbers. I read somewhere that intersections in set analysis don't work when the data is in the same table, but I also tried fully removing the Audit table and the formulas worked perfectly fine.


      Any help will be appreciated. I'm running Qlik Sense 3.2 SR2 for reference.


      Best regards,


      Justin