Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

carm1988
New Contributor III

Set analysis question, more elegant solution?

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

1 Reply

Re: Set analysis question, more elegant solution?

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

Community Browser