topic Re: Learning Set Analysis in QlikView

Learning Set Analysis

sunny_talwar — Fri, 10 Jul 2015 11:17:23 GMT

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

swuehl‌ MarcoWedel‌

Re: Learning Set Analysis

datanibbler — Fri, 10 Jul 2015 11:29:06 GMT

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.

Re: Learning Set Analysis

swuehl — Fri, 10 Jul 2015 11:30:11 GMT

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

Re: Learning Set Analysis

datanibbler — Fri, 10 Jul 2015 11:37:06 GMT

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!

Re: Learning Set Analysis

sunny_talwar — Fri, 10 Jul 2015 11:41:49 GMT

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

Re: Learning Set Analysis

swuehl — Fri, 10 Jul 2015 11:43:37 GMT

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

See attached.

Re: Learning Set Analysis

datanibbler — Fri, 10 Jul 2015 11:52:11 GMT

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

Re: Learning Set Analysis

sunny_talwar — Fri, 10 Jul 2015 12:08:33 GMT

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

Re: Learning Set Analysis

MarcoWedel — Sun, 12 Jul 2015 22:04:57 GMT

Hi,

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

regards

Marco

Re: Learning Set Analysis

sunny_talwar — Sun, 12 Jul 2015 22:06:49 GMT

No big deal MarcoWedel‌. Thanks for trying though

Best,

Sunny

Re: Learning Set Analysis

MarcoWedel — Mon, 13 Jul 2015 17:53:17 GMT

Hi,

second thought:

Another solution could be:

Concat({$<ID=P({$<POS={$(=Concat(Distinct POS,'}>})*P({$<POS={'))}>})>}DISTINCT ID,',')

hope this helps

regards

Marco

Re: Learning Set Analysis

sunny_talwar — Mon, 13 Jul 2015 18:03:36 GMT

Hahahaha awesome, Marco would you mind explaining the highlighted part:

=Concat({$<ID=P({$<POS={$(=Concat(Distinct POS,'}>})*P({$<POS={'))}>})>}DISTINCT ID,',')

actually {'} this is the main part I don't understand.

Thanks,

Sunny

Re: Learning Set Analysis

swuehl — Mon, 13 Jul 2015 19:24:05 GMT

The single quote ends the concat delimiter. Try to expand the dollar sign expansion.

Re: Learning Set Analysis

sunny_talwar — Mon, 13 Jul 2015 19:32:27 GMT

Oh wow.... That is awesome

I never thought of using a delimiter is such a manner. Kudos Marco‌

Thanks Stefan for helping me understand the expression.

Best,

Sunny

Re: Learning Set Analysis

MarcoWedel — Mon, 13 Jul 2015 22:28:59 GMT

Hi,

maybe helpful for other readers also:

The $-expansion evaluates to an expression that together with the leading and trailing code builds a dynamic set expression for the outer concat function.

It generates an intersection of possible IDs for each of the selected POS values, e.g. if A, B and C are selected the resulting expression looks like this:

Concat({$<ID=P({$<POS={A}>})*P({$<POS={B}>})*P({$<POS={C}>})>}DISTINCT ID,',')

hope this helps

regards

Marco