10 Replies Latest reply: Sep 3, 2017 3:23 PM by Carmelle Morrison

# Complex set analysis question please....

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!!

• ###### Re: Complex set analysis question please....

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))

• ###### Re: Complex set analysis question please....

awesome!!!!!

• ###### Re: Complex set analysis question please....

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

Thanks

• ###### Re: Complex set analysis question please....

Did you look at the screenshot here

• ###### Re: Complex set analysis question please....

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

• ###### Re: Complex set analysis question please....

QVF with dynamic sets / aggregation.

Thanks

• ###### Re: Complex set analysis question please....

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))

• ###### Re: Complex set analysis question please....

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

• ###### Re: Complex set analysis question please....

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?

• ###### Re: Complex set analysis question please....

Hi,

The overall goal is to find comparative records with the same field values as the record selected.

Each record has a unique ID, but up to 50 fields within the data model holding metadata for that record.

For that reason, the only distinct value is ID, all other metadata fields are non distinct.

The comparative records are based on the seven fields quoted within the set, and if a '100% match' is selected for any of those fields, the resulting comparative record must have the same value for that field as within the selection.

As a user can select more than 1 ID to gain comparative results for each based on those seven fields, it is required that we display which selection the result is associated to.

I am unsure if we can do this currently, as there is no way to make each selection unique  for the seven fields quoted within the set, and therefore no way of pointing the result to the correct selection and selected ID.

Thanks

Get Outlook for Android<https://aka.ms/ghei36>