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

[Gethyn]

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.

[datanibbler]

Hi Gethyn,

that formula is a bit overwhelming - but if I spotted that correctly what the keyword "in" does - as it does in SQL, maybe you could confirm that? - is matching one specific value against a list of others and just returning "True" or "False", right?

=> I think the MATCH() function should help you in that case. It will return a numeric - depending on the position of the "comparison_value" in the list that your actual value can be matched with - but you could just query whether that is <>0 if you want just a TRUE/FALSE. The numeric could be used in a PICK() function if you have any need for choosing between different calculations to do ...

HTH

best regards,

DataNibbler

[Gysbert_Wassenaar · Accepted solution]

In Qlikview nstead of IN you use the match function: match([APSpecDescrID], '1230300091','1230300826', ..... ,'1230300808')