topic Complex set analysis question please.... in App Development

Complex set analysis question please....

cmorri1988 — Fri, 01 Sep 2017 15:13:59 GMT

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

sunny_talwar — Fri, 01 Sep 2017 15:16:26 GMT

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

cmorri1988 — Fri, 01 Sep 2017 15:24:48 GMT

awesome!!!!!

Re: Complex set analysis question please....

cmorri1988 — Fri, 01 Sep 2017 15:39:51 GMT

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

Thanks

Re: Complex set analysis question please....

sunny_talwar — Fri, 01 Sep 2017 16:16:14 GMT

Did you look at the screenshot here

Re: Complex set analysis question please....

cmorri1988 — Fri, 01 Sep 2017 16:37:08 GMT

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

cmorri1988 — Fri, 01 Sep 2017 16:39:33 GMT

QVF with dynamic sets / aggregation.

Thanks

Re: Complex set analysis question please....

sunny_talwar — Fri, 01 Sep 2017 16:50:21 GMT

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

cmorri1988 — Sun, 03 Sep 2017 09:47:53 GMT

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

sunny_talwar — Sun, 03 Sep 2017 17:29:28 GMT

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

cmorri1988 — Sun, 03 Sep 2017 19:23:00 GMT

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>