topic Re: Nested set analysis in QlikView

Nested set analysis

Mauritz_SA — Sat, 16 Nov 2024 05:13:40 GMT

Hi everyone

My question is regarding nested set analysis (if that is what you call it). Below is a simplified version of my table (with the rows I want in blue):

Code	Status	Inspection	Overdue
A1	1	No	Yes
A2	1	No	Yes
A3	1	No	No
A4	2	No	Yes
A5	2	No	Yes
A6	3	No	No
A7	3	No	No
A8	4	Yes	No
A9	4	Yes	Yes
A10	4	No	Yes

My set analysis rules in SQL type logic (mixed with qlik set analysis syntax) are as follows:

Count(codes)

WHERE

Overdue = {"Yes"}

AND

(Status = {"1","2","3"} OR (Status = {"4"} AND Inspection = {"Yes"}))

Which will then return 5 -> (A1, A2, A4, A5, A9)

Currently I have something like this:

COUNT({<Overdue = {"1"},Status = {"1","2","3"}>} Code)

However, I am unsure how I should add the part for OR (Status = {"4"} AND Inspection = {"Yes"})

Two comments:

This will be used as part of a self service solution so I would like to keep the conditions of the Master Items in the dashboard/front end and not in the load script.
I have quite a few conditions so I would like to avoid using something like the following (unless it is the best way of doing it):

COUNT({<Overdue = {"1"},Condition 2, Condition 3, Condition 4, Status = {"1","2","3"}>+<Overdue = {"1"},Condition 2, Condition 3, Condition 4, Status = {"4"},Inspection = {"Yes"}>} Code)

Regards,

Mauritz

Re: Nested set analysis

jensmunnichs — Fri, 16 Nov 2018 08:42:58 GMT

Hey Mauritzsa,

This seems to work for me using your sample data:

=COUNT(
{
<Overdue={"Yes"}>*
(<Status = {"1", "2", "3"}>+<Status = {"4"}, Inspection = {"Yes"}>)
}
Code)

The '*' in the set analysis statement means AND, so any other conditions that should always apply outside the whole OR thing should come after Overdue={"Yes"}. Haven't been able to test this as I don't know your other conditions but I think it works.

By the way, I think 'Qlik Community Help Discussions' is for help with the actual forums, for actual help with Qlikview, I think this is where you want to be: https://community.qlik.com/t5/QlikView/ct-p/qlikview

Re: Nested set analysis

Mauritz_SA — Fri, 16 Nov 2018 09:13:26 GMT

Hi Jensmunnichs

Thank you for the reply, it works! I did not know that you could nest/group conditions using round brackets - very useful. I am still finding my way around the new community, did not realise that you could use the navigation pane on the left to contextualise questions. Thanks for the tip (and the answer to my question).

Regards,

Mauritz

Re: Nested set analysis

jensmunnichs — Fri, 16 Nov 2018 09:11:09 GMT

Honestly, I didn't know you could do that either, sometimes you just have to try 🙂 And don't worry about posting to the wrong forum, I'm still getting used to this new community too, as I think most people are.