Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
mapratt82
Contributor

"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            

robin_hausdoerfer
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?

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,

robin_hausdoerfer
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 

...

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

Re: "Not Match" isn't working

My bad

mapratt82
Contributor

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
Contributor

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!

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