Learning Set Analysis

[sunny_talwar]

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‌

[MarcoWedel]

Hi,

second thought:

Another solution could be:

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

hope this helps

regards

Marco

[sunny_talwar]

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

[swuehl]

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

[sunny_talwar]

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

[MarcoWedel]

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