IsNum vs IsText

[melissapluke]

Hi all,

I have three fields in my script which are numbers. I've confirmed this by doing "isnum()" on each of the fields. They are the 3 columns on the right in this screenshot. However, in my script i am creating a new field called test by doing this: BPtest&MRNtest&datetest as test.

However, there are some results that are no longer considered numbers. If I do num(BPtest&MRNtest&datetest) as test in my script, those instances do not generate any number and are null. Any ideas why if the subparts are numbers why the whole isn't? And any ideas how to solve this?

[kaushiknsolanki]

Hi What I can see is because you are using string concatenation (&) sign to concatenate those numbers and thus some of the values may be treated by Qlik Engine as Text and not numbers.

I would suggest to put some delimiter while concatenating if you want all values to be text. For example.

BPtest&'-'&MRNtest&'-'&datetest as test.

If you need all values to be numbers, then try this.

Num(Num#(BPtest&MRNtest&datetest)) as test

[melissapluke]

Hi,

I do need all values to be numbers. I tried your suggestion and the result is "test" isn't calculated for those that weren't evaluated as numbers in the original example.

[mbespartochnyy]

That could be because of the date field. Try something like:

BPtest&MRNtest&Num(datetest) as test

That'll ensure that date will be converted into number. Otherwise the slashes in the date field (10/22/2020) could be what is making the combined value of three fields to be a string.

[melissapluke]

Hi,

This time they all calculated but none are considered a number now.

[Kushal_Chawda]

@melissapluke  try below

trim(BPtest)&trim(MRNtest)&trim(datetest) as test.

[melissapluke]

Sorry, that didn't work either. It gave the same results as the previous suggestion.

[Kushal_Chawda]

@melissapluke  would you be able to share sample app to look at? 

[melissapluke]

I couldn't share so I had to recreate a similar situation. It is still happening in this application. Thanks for taking a look!

[Kushal_Chawda · Accepted solution]

@melissapluke  see the attached