Decimal and thousand separators, trouble using IsNum()

[FernandaNava]

Hello,

So, I asked these two questions before:

1. I had regional settings problems: https://community.qlik.com/t5/New-to-Qlik-Sense/Min-Max-returning-multiple-values/m-p/1689730#M16146...

2. Using IsNum  https://community.qlik.com/t5/New-to-Qlik-Sense/Telling-apart-datatypes-on-same-field/m-p/1690720#M1...

These solutions worked on my PC with Americas regional settings, but ever since I moved on to an European server and and DB, I have been having trouble again. I understand that Qlik takes the regional settings of the pc, so I set the thousand separator to "." and the decimal to "," as it is in the windows settings. When I did this I kept getting the error from my first question, even after creating an application from scratch in the european computer. So I changed it back to a . as decimal separator and now I was able to do the master calendar, but I suspect this is the reason why IsNum is not working anymore (I get a 0 every time.

I apologize for mixing two subjects but they seem related to me. I hope I stated my problem with clarity. Thank you.

PS. the values on the database use ',', and I've also tried replacing the comma for a point during extraction, to no result. 

I'm attaching a sample of the data.

[madelonjansen]

Hi FerNava,

The IsNum() function is a great way to understand the format of your data, but it will not change the format.

When the values are not numerical (returning 0) but need to be, you can use the Num#() function to convert them.

[FernandaNava]

Thanks for your reply. The thing is, this field sometimes contains a number and sometimes text, and I was using IsNum to add a flag so I could later use those who are actually numbers. 

I'm trying to upload a sample of the data but it won;t let me. I saved it here.

[madelonjansen · Accepted solution]

To check if it's a number, we have to convert to number when possible.

I used this piece of script:

Which returns this:

To break it down:

num(Num#('0'&RISULTATO)) as Number

add a leading zero and using the Num#() function convert to numerical value. (if needed, add the source format in the 2nd parameter of the Num#() function.)

Then use this as the input for the IsNum() function, to check if it was actually a number:

if(IsNum(num(Num#('0'&RISULTATO))), 1, 0) as IsNumNumber

[FernandaNava]

Thank you, this worked perfectly!