topic Qlik Sense - Set Analysis Intersection Issue - Duplicates and Table Order in App Development

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

Anonymous — Fri, 16 Jun 2017 16:50:40 GMT

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

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

sunny_talwar — Fri, 16 Jun 2017 17:22:29 GMT

Not sure I understand the issue, but using this expression works for database 2

=Count({<[AssetID2]=P({<[View2] = {"Manufacturer"}>} [AssetID2])*E({<[View2] = {"Customer"}>} [AssetID2])*E({<[View2] = {"Collector"}>} [AssetID2])>} DISTINCT [AssetID2])

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

Anonymous — Fri, 16 Jun 2017 18:17:29 GMT

Hi Sunny,

That does work; thank you!

Do you know why removing the extra angle brackets/pieces of set analysis works compared to having them? That's the main thing I'm looking for - why doesn't the formula that I am using work only in Scenario 2?

Best regards,

Justin

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

sunny_talwar — Fri, 16 Jun 2017 18:18:41 GMT

To tell you the truth, I have no idea.... but it could very well be a bug....

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

Anonymous — Fri, 16 Jun 2017 18:21:28 GMT

Okay, no problem. I'll leave this open for a little bit longer to see if anyone can provide an explanation, but thank you so much for looking into it. I'll be updating the formula for the extension to use yours. And if I don't get an explanation for why, I'll mark yours as the correct answer. Thank you again!

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

sunny_talwar — Fri, 16 Jun 2017 18:24:17 GMT

Sounds like a plan... and I would be interested in knowing why it did not work...

Best,

Sunny