Set analysis question, more elegant solution? - Qlik Community

Anonymous · ‎2017-09-14

Hi All,

Wondering if anyone has a more elegant solution to the below.

17 set analysis scenarios, to return 'Rank' field numbers, and then finding the minimum 'Rank' field number within the resulting range of fields.

Thanks!

=if(len(If(Only({1<ID = p({<Genre = (p(Genre)), [Sub Genre] = (p([Sub Genre])),
Literal3 = (p(Literal3)),
ID = e(ID),Author = , Title =, ISBN =>})>}Aggr(Rank(Only({1<ID = p({<Genre = (p(Genre)), [Sub Genre] = (p([Sub Genre])),
Literal3 = (p(Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title = , ISBN = >})>}-ID)), Genre, [Sub Genre], Literal3, ID)) <= $(vResults),
concat(TOTAL <Genre, [Sub Genre], Literal3> ID,',')))>0,

If(Only({1<ID = p({<Genre = (p(Genre)), [Sub Genre] = (p([Sub Genre])),
Literal3 = (p(Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title = , ISBN = >})

>}Aggr(Rank(Only({1<ID = p({<Genre = (p(Genre)), [Sub Genre] = (p([Sub Genre])),
Literal3 = (p(Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title = , ISBN = >})>}-ID)),

Genre, [Sub Genre], Literal3, ID)) <= $(vResults),

rangemin(

//One
num(only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj2_code = (p(bisac_sbj2_code)),bisac_sbj3_code = (p(bisac_sbj3_code)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank1))
,
//Two
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj2_code = (p(bisac_sbj2_code)),bisac_sbj3_code = (p(bisac_sbj3_code)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank2)
,
//Three
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj2_code = (p(bisac_sbj2_code)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),Level3_Literal1Literal3 = (p(Level3_Literal1Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank3)
,
//Four
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj2_code = (p(bisac_sbj2_code)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank4)
,
//Five
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj2_code = (p(bisac_sbj2_code)),
Level3_Literal1 = (p(Level3_Literal1)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank5)
,
//Six
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj3_code = (p(bisac_sbj3_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),Level2_Literal1Literal3 = (p(Level2_Literal1Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank6)
,
//Seven
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj3_code = (p(bisac_sbj3_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank7)
,
//Eight
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),bisac_sbj3_code = (p(bisac_sbj3_code)),
Level2_Literal1 = (p(Level2_Literal1)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank8)
,
//Nine
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),Level2_Literal1Literal3 = (p(Level2_Literal1Literal3)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),Level3_Literal1Literal3 = (p(Level3_Literal1Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank9)
,
//Ten
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),Level2_Literal1Literal3 = (p(Level2_Literal1Literal3)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank10)
,
//Eleven
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),Level2_Literal1Literal3 = (p(Level2_Literal1Literal3)),
Level3_Literal1 = (p(Level3_Literal1)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank11)
,
//Twelve
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),Level3_Literal1Literal3 = (p(Level3_Literal1Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank12)
,
//Thirteen
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank13)
,
//Fourteen
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),Level2_Literal2 = (p(Level2_Literal2)),
Level3_Literal1 = (p(Level3_Literal1)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank14)
,
//Fiveteen
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),Level3_Literal1Literal3 = (p(Level3_Literal1Literal3)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank15)
,
//Sixteen
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),
Level3_Literal1 = (p(Level3_Literal1)),Level3_Literal2 = (p(Level3_Literal2)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank16)
,
//Seventeen
only({1<ID = p({<bisac_sbj1_code = (p(bisac_sbj1_code)),
Level2_Literal1 = (p(Level2_Literal1)),
Level3_Literal1 = (p(Level3_Literal1)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}Rank17))

,null()),null())

sunny_talwar · ‎2017-09-14

Just looking at the expression makes it difficult to see what is going on, but one suggestion I can make is to convert Rank1, Rank2, .... Rank17 into a field and its value in another field using The Crosstable Load