topic Re: Decimal and thousand separators, trouble using IsNum() in App Development

Decimal and thousand separators, trouble using IsNum()

FernandaNava — Sat, 16 Nov 2024 18:45:32 GMT

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#M161468

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

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.

Re: Decimal and thousand separators, trouble using IsNum()

madelonjansen — Tue, 21 Apr 2020 11:58:56 GMT

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.

Re: Decimal and thousand separators, trouble using IsNum()

FernandaNava — Tue, 21 Apr 2020 12:21:22 GMT

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.

Re: Decimal and thousand separators, trouble using IsNum()

madelonjansen — Tue, 21 Apr 2020 12:32:06 GMT

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

Re: Decimal and thousand separators, trouble using IsNum()

FernandaNava — Tue, 21 Apr 2020 12:42:32 GMT

Thank you, this worked perfectly!