Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cmorri1988
Creator
Creator

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
IDTypeClassSales
11A10
21A10
31A10
42C20
52C30
62C10
72C21
83D34
93D21
104D10
Resulting Table
IDTypeClassSales
21A10
31A10
52C30
62C10

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

TypeClassSalesSelected ID
21A101
31A101
52C304
62C10  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!!

10 Replies
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
Creator
Creator
Author

awesome!!!!!

cmorri1988
Creator
Creator
Author

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

Thanks

sunny_talwar

Did you look at the screenshot here

Capture.PNG

cmorri1988
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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

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?