topic Re: nested set analysis - question in QlikView

nested set analysis - question

Wed, 20 Jan 2016 22:48:09 GMT

I followed FABRICE44 document and used nested set analysis.

See below for an excerpt from it.

It works perfect, if the field is numbers but not text.

avg({<[Lot Number]={"=only({<[Sample ID]={'CHS-013'}>}[Lot Number])"}, [Sample ID] = {"CHS-022"}> }RESULT_TEXT)

In the above, Lot Number, Sample ID and RESULT_TEXT are fields. I am trying to get Lot Numbers where the Sample ID is 'CHS-013' and then find the Lot numbers that have a Sample ID of 'CHS-022'. This works fine if the Lot Number field is numbers, but does not if the Lot Numbers field is text.

Please help.

Natraj

I have spent over 20 hrs with this!

But we can also decide to search the MANUFACTURERs on a specific period, or for specific products. We

will create an inner set:

sum( {$ <MANUFACTURER_LDESC={"=sum({1<TIME_SDESC={'P 01/13'},

CATEGORY_LDESC={'ACC','CHEESE CAKE’ }>} [Volume Sales])>100000"} >} [Volume Sales])

Remember that the members could be enclosed between single or double quotes. Because the function

uses double quotes, we will use for the members either the single quotes or the square brackets [].

I want to find the Manufacturers whose sales are over 100,000 for the 2 categories CHEESE CAKE and

ACC in January 2013 (period P01/13). But I want to remove from that list those whose sales are lower than

50,000 for all categories in January 2012:

I need two sets : {<set 1> - <set 2>}, each of them will use the function sum():

Set 1 = 1 <MANUFACTURER_LDESC={"=sum({1< TIME_SDESC={'P

01/13'},CATEGORY_LDESC={'ACC','CHEESE CAKE'}>} [Value Sales])>50000"} >

Set 2 = <MANUFACTURER_LDESC={"=sum({1< TIME_SDESC={'P 01/12'},CATEGORY_LDESC={'*'}>}

[Value Sales])>100000"} >}

Re: nested set analysis - question

Peter_Cammaert — Wed, 20 Jan 2016 23:42:21 GMT

Can you please explain what you are trying to accomplish with this expression? On first reading, there seem to be a number of contradictions which I cannot explain. And your code has not much to do with the example at the bottom, except for having two set modifiers too.

Thanks,

Peter

Re: nested set analysis - question

swuehl — Wed, 20 Jan 2016 23:55:03 GMT

It should also work with text values, when you are using a dollar sign expansion instead of an advanced search that evaluates to a boolean expression:

avg({<[Lot Number]={'$(=only({<[Sample ID]={'CHS-013'}>}[Lot Number]))'}, [Sample ID] = {"CHS-022"}> }RESULT_TEXT)

I agree with Peter that I don't fully understand the business logic of your set expression.

Re: nested set analysis - question

Thu, 21 Jan 2016 02:45:22 GMT

Apologize for the cryptic question.

I tried the $ expansion thing you suggest - no luck.

Here is the table (from excel)

Lot Number	Sample ID	RESULT_TEXT
1	CHS-013	3
2	CHS-013	3
3	CHS-013	3
4	CHS-013	3
5	CHS-013	3
6	CHS-013	3
7	CHS-013	3
1	CHS-022	5
2	CHS-022	5
3	CHS-022	5
4	CHS-022	5
5	CHS-022	5
6	CHS-022	5
7	CHS-022	5
8	CHS-022	5
9	CHS-022	5
10	CHS-022	5
11	CHS-022	5
12	CHS-155	8
13	CHS-155	8
14	CHS-155	8
15	CHS-155	8
16	CHS-155	8
17	CHS-155	8
18	CHS-155	8
19	CHS-155	8
20	CHS-155	8

Here is what I like to do:

1. Obtain the Lot Numbers corresponding to Sample ID = CHS-013

2. Find the Result_text for Sample ID = CHS-022 corresponding to the Lot Numbers obtained for Sample ID = CHS-013

My approach:

The Inner Set will do the first part:

{"=only({<[Sample ID]={'CHS-013'}>}[Lot Number])"}

This will return a list of Lot Numbers that correspond to the Sample ID = CHS-013

avg({<[Lot Number]={"=only({<[Sample ID]={'CHS-013'}>}[Lot Number])"}, [Sample ID] = {"CHS-022"}> }RESULT_TEXT)

Now if I put it into the outer set that will look for these Lot Numbers AND the condition that Sample ID = CHS-013, then I will end up with the Result_Text corresponding to the Sample ID = CHS-022, but only for the Lot Numbers corresponding to CHS-013.

Again, this works fine if my Lot Numbers are 1, 2 , 3.. 20. But if I change it to A, B, C, D etc, it doesnt work.

Number works

Text and $ expansion dont work.

Thanks for your help..

Natraj

Re: nested set analysis - question

swuehl — Thu, 21 Jan 2016 11:04:29 GMT

If you want to stick to your advanced search:

avg({<[Lot Number]={=not isnull(only({<[Sample ID]={'CHS-013'}>}[Lot Number]))"}, [Sample ID] = {"CHS-022"}> }RESULT_TEXT)

Re: nested set analysis - question

Thu, 21 Jan 2016 15:22:39 GMT

Your suggestion worked! with the not isnull. What is the reason? How does

it work?

Is there another way to do this at the table level..

I am unable to logon to qlik community now...

Natraj

Re: nested set analysis - question

Thu, 21 Jan 2016 15:29:56 GMT

I am also unable to mark your answer as correct!

Thanks..

Natraj

Re: nested set analysis - question

swuehl — Thu, 21 Jan 2016 18:29:55 GMT

You can also use the p() function:

avg({<[Lot Number]= p({<[Sample ID]={'CHS-013'}>} ), [Sample ID] = {"CHS-022"}> } RESULT_TEXT)

Re: nested set analysis - question

Thu, 21 Jan 2016 22:13:14 GMT

Thanks again for your help. This is not giving me an option to mark your answer correct!