Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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.

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

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
Highlighted
Partner
Partner

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

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.

 

 

Highlighted
Partner
Partner

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

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.

Highlighted
Partner
Partner

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

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

Highlighted
Partner
Partner

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

Thank you, this worked perfectly!