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

[cmorri1988]

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>