2 Replies Latest reply: Mar 5, 2015 5:20 AM by Gysbert Wassenaar RSS

    Can I use 'in' in the load statement or chart expression?

    Gethyn Owen

      Hi All,

       

      I am trying to replace one of my old Crystal reports with a new QlikView model to give me the same data output but with more flexibility. I have hit a problem though, I don't seem to have a way of replicating a formula from Crystal that is used to create a calculated field, as far as I can tell the problem is that I can't use in during the script load or in an expression. If I replace the function of the in by using = and expanding the line count I think I go beyond an upper limit for nested Ifs.

       

      Here is the original Crystal formula which works:

       

      if ({@Case Type} = "") then {MEDA.PROC_CODE} else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PM") then "No Charge"+" "+{@Case Type} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PL") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PF") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PB") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PA") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "NP") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PX") AND ({@APSpecDescrID} = "407") then "H" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PX") AND ({@APSpecDescrID} = "256") then "H" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PX") AND ({@APSpecDescrID} = "1230300202") then "C" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PX") AND ({@APSpecDescrID} = "257") then "C" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PX") AND ({@APSpecDescrID} = "Invalid") then "R" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PX") AND ({@APSpecDescrID} = "1230300205") then "S" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PX") AND ({@APSpecDescrID} = "254") then "S" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PS") AND ({@APSpecDescrID} IN ["1230300091",

                                                                                                     "1230300826",

                                                                                                     "1230300807",

                                                                                                     "1230300809",

                                                                                                     "1230300815",

                                                                                                     "1230300809",

                                                                                                     "1230300808"]) then {MEDA.HistoComponentResult4181} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PS") AND NOT({@APSpecDescrID} IN ["1230300091",

                                                                                                        "1230300826",

                                                                                                        "1230300807",

                                                                                                        "1230300809",

                                                                                                        "1230300815",

                                                                                                        "1230300809",

                                                                                                        "1230300808"]) then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if (({@Contract Type} = "Contract") AND ({@Case Type} = "PQ") AND NOT isnull ({MEDA.HistoComponentResult4181})) then {MEDA.HistoComponentResult4181} Else

              if (({@Contract Type} = "Contract") AND ({@Case Type} = "PQ") AND isnull ({MEDA.HistoComponentResult4181})) then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PN") AND ({@APSpecDescrID} = "1230300802") then "MUSBX" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PN") AND ({@APSpecDescrID} = "1230300100") then "NERBX" Else

              if ({@Contract Type} = "Contract") AND ({@Case Type} = "PN") AND NOT({@APSpecDescrID} IN ["1230300802", "1230300100"]) then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

       

       

              if ({@Contract Type} = "Private") AND ({@Case Type} = "PX") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Private") AND NOT({@Case Type} = "PX") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

       

       

              if ({@Contract Type} = "GP") AND ({@Case Type} = "NP") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PB") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PF") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PM") then "No Charge"+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PS") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PA") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PN") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PQ") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "GP") AND ({@Case Type} = "PX") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

       

       

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "NP") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PB") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PF") then "Standard Code"+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PM") then "No Charge"+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PS") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PA") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PN") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PQ") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else

              if ({@Contract Type} = "Acute internal cross charge") AND ({@Case Type} = "PX") then Complexity ({MEDA.HistoComponentResult363})+" "+{@Case Type} Else{MEDA.PROC_CODE}

       

      Thanks in advance,

       

      Gethyn.