Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- nested set analysis - question

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2016-01-20
05:48 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

nested set analysis - question

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"} >}

704 Views

8 Replies

Peter_Cammaert

Partner - Champion III

2016-01-20
06:42 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

swuehl

MVP

2016-01-20
06:55 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

512 Views

Not applicable

2016-01-20
09:45 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

512 Views

swuehl

MVP

2016-01-21
06:04 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

Not applicable

2016-01-21
10:22 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

512 Views

Not applicable

2016-01-21
10:29 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I am also unable to mark your answer as correct!

Thanks..

Natraj

512 Views

swuehl

MVP

2016-01-21
01:29 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You can also use the p() function:

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

Not applicable

2016-01-21
05:13 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

512 Views