12 Replies Latest reply: May 4, 2017 12:15 PM by mark pratt RSS

    "Not Match" isn't working

    mark pratt

      I can't get the following to work:

       

        If((

           (Rate_Index = 02 and Not Wildmatch(Interest_Rate, 0.000000, 0.001000, 0.002000, 0.003000)) or             

           (Rate_Index = 03 and Not Wildmatch(Interest_Rate, 0.000000, 0.001000)) or

           (Rate_Index = 04 and Not Wildmatch(Interest_Rate, 0.001000, 0.002000, 0.003000, 0.005000)) or

           (Rate_Index = 07 and Not Wildmatch(Interest_Rate, 0.000000, 0.001000)) or

           (Rate_Index = 08 and Not Wildmatch(Interest_Rate, 0.001000)) or

           (Rate_Index = 13 and Not Wildmatch(Interest_Rate, 0.010000, 0.001500)) or

           (Rate_Index = 34 and Not Wildmatch(Interest_Rate, 0.001000, 0.003000))), 1, 0) as Error_Interest_Rate,

       

      I have this in the Load statement. I have tried using " ' " doesn't help. It appears to give everything a '1', regardless of Interest_Rate.

       

      Thanks

        • Re: "Not Match" isn't working
          Craig Sutton

          Check to see if wildmatch = 0.  Wildmatch returns 0 when there is no match.

           

             (Rate_Index = 02 and Wildmatch(Interest_Rate, 0.000000, 0.001000, 0.002000, 0.003000)=0) or            

          • Re: "Not Match" isn't working
            Robin Hausdörfer

            you're sure that the text representation of Rate_Index and Interest_Rate is exactly the way you filter?

            • Re: "Not Match" isn't working
              Sunny Talwar

              May be add single quotes around the searched numbers

               

              If((

                  (Rate_Index = '02' and Not Wildmatch(Interest_Rate, '0.000000', '0.001000', '0.002000', '0.003000')) or          

                  (Rate_Index = '03' and Not Wildmatch(Interest_Rate, '0.000000', '0.001000')) or

                  (Rate_Index = '04' and Not Wildmatch(Interest_Rate, '0.001000', '0.002000', '0.003000', '0.005000')) or

                  (Rate_Index = '07' and Not Wildmatch(Interest_Rate, '0.000000', '0.001000')) or

                  (Rate_Index = '08' and Not Wildmatch(Interest_Rate, '0.001000')) or

                  (Rate_Index = '13' and Not Wildmatch(Interest_Rate, '0.010000', '0.001500')) or

                  (Rate_Index = '34' and Not Wildmatch(Interest_Rate, '0.001000', '0.003000'))), 1, 0) as Error_Interest_Rate,

              • Re: "Not Match" isn't working
                Jonathan Dienst

                WildMatch is a tesxt comparison, you have numeric values. Unless the loaded format of the numbers is exactly as shown in the WildMatch, they will not match.I would try

                 

                ...

                (Rate_Index = '02' and Not Wildmatch(Num(Interest_Rate), 0, 0.001, 0.002, 0.003)) or 

                ...

                 

                or

                ...

                (Rate_Index = 2 and Not Wildmatch(Num(Interest_Rate), 0, 0.001, 0.002, 0.003)) or 

                ...

                • Re: "Not Match" isn't working
                  mark pratt

                  Thanks for all the responses so far, unfortunately none of the solutions have worked thus far. I have tried the following in the App and it seems to give me the response I was expecting, so really leaves me puzzled why it isn't working in the load.

                   

                  Count(distinct(If((

                    (Rate_Index = 02 and Not Wildmatch(Interest_Rate, 0.000000, 0.001000, 0.002000, 0.003000)) or

                    (Rate_Index = 03 and Not Wildmatch(Interest_Rate, 0.000000, 0.001000)) or

                    (Rate_Index = 04 and Not Wildmatch(Interest_Rate, 0.001000, 0.002000, 0.003000, 0.005000)) or

                    (Rate_Index = 07 and Not Wildmatch(Interest_Rate, 0.000000, 0.001000)) or

                    (Rate_Index = 08 and Not Wildmatch(Interest_Rate, 0.001000)) or

                    (Rate_Index = 13 and Not Wildmatch(Interest_Rate, 0.010000, 0.001500)) or

                    (Rate_Index = 34 and Not Wildmatch(Interest_Rate, 0.001000, 0.003000))),DDA_Account)))

                   

                  Keep the thoughts coming, and thanks everyone again.

                  • Re: "Not Match" isn't working
                    mark pratt

                    We have determined that it must have something to do with Qlik and Wildmatch not liking a decimal. I even tried setting interest_rate as a string using the Text function and single quotes on the value and still it didn't work. So we decided to just use the following.

                     

                        If((

                      (Rate_Index = 02 and (Interest_Rate <> 0.000000 and Interest_Rate <> 0.001000 and Interest_Rate <> 0.002000 and Interest_Rate <> 0.003000)) or

                      (Rate_Index = 03 and (Interest_Rate <> 0.000000 and Interest_Rate <> 0.001000)) or

                      (Rate_Index = 04 and (Interest_Rate <> 0.001000 and Interest_Rate <> 0.002000 and Interest_Rate <> 0.003000 and Interest_Rate <> 0.005000)) or

                      (Rate_Index = 07 and (Interest_Rate <> 0.000000 and Interest_Rate <> 0.001000)) or

                      (Rate_Index = 08 and Interest_Rate <> 0.001000) or

                      (Rate_Index = 13 and (Interest_Rate <> 0.010000 and Interest_Rate <> 0.001500)) or

                      (Rate_Index = 34 and (Interest_Rate <> 0.001000 and Interest_Rate <> 0.003000))),

                      1, 0) as Error_Interest_Rate,

                     

                    Maybe someone can figure it out eventually, but this is the only thing we could get to function properly in the Load. Thanks again everyone!