Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
you're sure that the text representation of Rate_Index and Interest_Rate is exactly the way you filter?
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,
he wrote that he tried single quotes yet....
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
...
My bad
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.
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!
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...