Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mapratt82
Creator
Creator

"Not Match" isn't working

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

12 Replies
craigsutton
Creator
Creator

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            

Anonymous
Not applicable

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

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,

Anonymous
Not applicable

he wrote that he tried single quotes yet....

jonathandienst
Partner - Champion III
Partner - Champion III

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 

...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar

My bad

mapratt82
Creator
Creator
Author

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.

mapratt82
Creator
Creator
Author

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!

Anonymous
Not applicable

there could be blanks in the text representation,

there could be the same number loaded with another text representation before ...

Please upload a small sample of your data with the fields mentioned above...