1 Reply Latest reply: Sep 14, 2017 12:37 PM by Sunny Talwar RSS

    Set analysis question, more elegant solution?

    Craig Murray

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