Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
sunny_talwar

Learning Set Analysis

Hey guys -

This may be a very easy question, but I never really understood how this would work. While working on a community thread, I was stuck here and didn't know how I would do this. So I created a sample and thought some of the experts out there might be able to help. Lets say I have table like this:

Table:

LOAD * Inline [

POS, ID

A, 1234

B, 1234

C, 1234

A, 1232

C, 1232

A, 1991

B, 1991

B, 1221

C, 1221

];


In the above table, ID 1234 is available in all the POS, but rest of them are only available in some combination of POS, but not all. I am text box where I use an expression =Concat(DISTINCT ID, '|') to show all distinct ID. But now I would like to add a set analysis statement which would show me only IDs which are present in all of my selected POS. So when nothing is selected I should only see 1234, but when I select A and B, I should see 1234 and 1991.

I hope I was able to give a good explanation of my questions above. I am also attaching the sample for ease.

Thanks for your responses.

Best,

Sunny

swuehlMarcoWedel

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Hi Sunny,

are you looking for something like this? I think I've seen the other thread,too.

edit:

This is the expression

=Concat({<ID = {"=count(distinct POS)=count(TOTAL DISTINCT POS) "}>}DISTINCT ID, '|')

Guess Marco will find more alternative solutions

View solution in original post

14 Replies
datanibbler
Champion
Champion

Hi sunindia,

I guess that would be a use_case for the AND mode in listboxes. I haven't yet really grasped that concept, so I guess it will be helpful for me as well if I work through your sample which I'm doing now.

I'll be back here when I have found out how to do it.

swuehl
MVP
MVP

Hi Sunny,

are you looking for something like this? I think I've seen the other thread,too.

edit:

This is the expression

=Concat({<ID = {"=count(distinct POS)=count(TOTAL DISTINCT POS) "}>}DISTINCT ID, '|')

Guess Marco will find more alternative solutions

datanibbler
Champion
Champion

Hi swuehl,

I kind of understand that set_expression - logically, if the COUNT of a field with the dimension active is the same as the Count with the dimension ignored, then that field must be present across all the dimension_values.

I am trying to do this using the AND mode - that should also be possible, no? I mean, then I can select all the IDs which remain available with all the POs I have selected.

But the checkbox is inactive - what do I have to do first to activate this option in a listbox?

Thanks a lot!

sunny_talwar
Author

Hahahaha you know what I am talking about because you were the first one to answer on the thread while I was still struggling on it .

I swear to god, I thought there was another way to do this using p() or e(), but I might be mistaken here. This definitely gives the solution I was looking for, but I would love to wait for Marco's alternative solutions .

Thanks Stefan for looking into this one.

Best,

Sunny

swuehl
MVP
MVP

There are some prerequisites your data model needs to fulfill, in this case, a LOAD DISTINCT should be enough.

See attached.

datanibbler
Champion
Champion

Ah, yes, I see. That was in the bog_post by Jennell that I read, but one always needs to hear the important things several times.

Thank you!

Best regards,

DataNibbler

sunny_talwar
Author

Huh!! Why is this the first time I am seeing this? I guess it's not used very often. So If I add DISTINCT to my load the selection on A become AND mode? DataNibbler‌ would you be able share the blog you are talking about.

Thanks both of you.

Best,

Sunny

MarcoWedel

Hi,

I didn't find a shorter solution than Stefan did.

regards

Marco

sunny_talwar
Author

No big deal MarcoWedel‌. Thanks for trying though

Best,

Sunny