Complex set analysis question please....

[cmorri1988]

Hi there,

I select several ID's and return 2 ID's for each ID selected, where Type and Class value match the selected ID.

The below example shows the selection of ID's 1 and 4 to return:

ID's 2 and 3 (first two records with same possible values for Type and Class as ID 1

ID's 5 and 6 (first two records same possible values for Type and Class as ID 4

Data
ID	Type	Class	Sales
1	1	A	10
2	1	A	10
3	1	A	10
4	2	C	20
5	2	C	30
6	2	C	10
7	2	C	21
8	3	D	34
9	3	D	21
10	4	D	10
Resulting Table
ID	Type	Class	Sales
2	1	A	10
3	1	A	10
5	2	C	30
6	2	C	10

I do this with,

If(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}

Aggr(Rank(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}-ID)), Type, Class, ID)) < 3,

Sum({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}[Sales]))

I would like to display the selected ID relevant to the result, such as...

ID	Type	Class	Sales	Selected ID
2	1	A	10	1
3	1	A	10	1
5	2	C	30	4
6	2	C	10	4

Please can anyone offer some advice on the correct set analysis expression to use?

I have also attached a small test Qlik Sense QVF.

Thanks!!

[sunny_talwar]

Check my response here:

Complex set analysis question please....

If(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}Aggr(Rank(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}-ID)), Type, Class, ID)) < 3,

Only(TOTAL <Type, Class> ID))

[cmorri1988]

awesome!!!!!

[cmorri1988]

Seems to work for one ID selection, but not for more than 1.

Thanks

[sunny_talwar]

Did you look at the screenshot here

[cmorri1988]

Apologies, yes I can see that now, and works perfectly.

When I migrate into my application, I get no results for more than one selection, but I am using dynamic sets and aggregations.

I understand this surpasses the remit of this question which you have correctly answered, but have attached the application should you wish to take a look.

The user selects from the '* Match' filters to dynamically change the set analysis between p(field) and {'*'}

This also then dynamically changes the aggregation, where if {'*'}, this is removed from the aggregation field string.

Thanks again

[cmorri1988]

QVF with dynamic sets / aggregation.

Thanks

[sunny_talwar]

Try this

=If(Only({1<ID = p({<Genre =  ($(=$(vBISACMatch))), [Sub Genre] = ($(=$(vBISAC2Match))),

Literal3 = ($(=$(vBISAC3Match))),Format = ($(=$(vFormatMatch))),Audience = ($(=$(vAudienceMatch))),

[Audience Category] = ($(=$(vAudienceCategoryMatch))),[Series Flag] = ($(=$(vSeriesFlagMatch))),

ID = e(ID)>})>}Aggr(Rank(Only({1<ID = p({<Genre =  ($(=$(vBISACMatch))), [Sub Genre] = ($(=$(vBISAC2Match))),

Literal3 = ($(=$(vBISAC3Match))),Format = ($(=$(vFormatMatch))),Audience = ($(=$(vAudienceMatch))),

[Audience Category] = ($(=$(vAudienceCategoryMatch))),[Series Flag] = ($(=$(vSeriesFlagMatch))),

ID = e(ID)>})>}-ID)), $(vAggr))) <= $(vResults),

Only(TOTAL <Type, Class> ID))

[cmorri1988]

Thanks,

This seems to work ok, until you select two ID's with the same

Genre,

Sub Genre,

Literal3,

Audience,

Audience Category,

Format,

Series Flag,

Type,

Class

I have tried incorporating BTKEY / ISBN into the expression, but this doesn't seem to resolve the above issue...

=If(Only({1<ID = p({<Genre =  ($(=$(vBISACMatch))), [Sub Genre] = ($(=$(vBISAC2Match))),

Literal3 = ($(=$(vBISAC3Match))),Format = ($(=$(vFormatMatch))),Audience = ($(=$(vAudienceMatch))),

[Audience Category] = ($(=$(vAudienceCategoryMatch))),[Series Flag] = ($(=$(vSeriesFlagMatch))),

ID = e(ID)>})>}Aggr(Rank(Only({1<ID = p({<Genre =  ($(=$(vBISACMatch))), [Sub Genre] = ($(=$(vBISAC2Match))),

Literal3 = ($(=$(vBISAC3Match))),Format = ($(=$(vFormatMatch))),Audience = ($(=$(vAudienceMatch))),

[Audience Category] = ($(=$(vAudienceCategoryMatch))),[Series Flag] = ($(=$(vSeriesFlagMatch))),

ID = e(ID)>})>}-ID)), $(vAggr))) <= $(vResults),

Only(TOTAL <Type, Class, BTKEY, ISBN> ID))

Thanks Again

[sunny_talwar]

You want same id based on Type and Class... where did these other fields came in from? I am not sure what your final goal is? Do you mind the overall goal?