Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FernandaNava
Partner - Contributor III
Partner - Contributor III

Decimal and thousand separators, trouble using IsNum()

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.

Labels (2)
1 Solution

Accepted Solutions
madelonjansen
Partner - Contributor II
Partner - Contributor II

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

I used this piece of script:

madelonjansen_0-1587472125994.png

 

Which returns this:

madelonjansen_1-1587472157117.png

 

 

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

 

View solution in original post

4 Replies
madelonjansen
Partner - Contributor II
Partner - Contributor II

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor II
Partner - Contributor II

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

I used this piece of script:

madelonjansen_0-1587472125994.png

 

Which returns this:

madelonjansen_1-1587472157117.png

 

 

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
Partner - Contributor III
Partner - Contributor III
Author

Thank you, this worked perfectly!