Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mapratt82
New Contributor III

"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
Contributor

Re: "Not Match" isn't working

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            

roharoha
Valued Contributor III

Re: "Not Match" isn't working

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

Highlighted
MVP
MVP

Re: "Not Match" isn't working

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,

roharoha
Valued Contributor III

Re: "Not Match" isn't working

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

MVP
MVP

Re: "Not Match" isn't working

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 

...

MVP
MVP

Re: "Not Match" isn't working

My bad

mapratt82
New Contributor III

Re: "Not Match" isn't working

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
New Contributor III

Re: "Not Match" isn't working

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!

roharoha
Valued Contributor III

Re: "Not Match" isn't working

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...