string format issue in qliksense

[nitin_2409]

Hi,

i am getting below results from a sql query in data load editor:

30

66.2%

78.98%

32.78

16.7

these all values with different formatting are part of a single field, hence the field returned from sql is in string format.

however, if i apply some filters on this field and suppose the result is(all integers like values):

30

43

51

then qlik shows the values as:

30.0

43

51

so my question is why it is appending decimal places in some values or why it is treating the filed as integer/number. i am explicitly converting this particular field into string via sql.

Also this issue is intermittent, sometimes it happens and sometimes it does not.(i have many fields like such, if happens randomly with any field)

Even after using text function in expression editor or in loading data through data load editor the results are same. Somehow the qlik data engine converts the value 30 to 30.0

let me know if anyone has faced this scenario or has solution to this condition

Appreciate the feedback.

Thank you

Nitin

[dplr-rn]

Qlik typically gets the output from sql and interprets the data and assigns the data type.

Whats the data type in qlik data model viewer for this column? if database column has some subtleties such mixed formats can occur.

you can you num function in the qlik load script to create in appropriate format

[nitin_2409]

Hi Dilip,

Thanks for the feedback.

The data type is data model(from tags column) is coming as $ascii$text.

I cannot use num function because there are some values within the same column which are text (e.g. like 'na', 'xy')

similar issue i found:

String/Number format problem in Qlikview

Please suggest

Thank you

[dplr-rn]

I would purge the alpha values values (create a different column if needed) and use that

use isnum or istext functions to identify

[nitin_2409]

Hi Dilip,

can you please give an example to do it ?

also in my case i am doing all the calculations in sql instead of qlik script functions in data load editor.

when i execute the same sql in the sql application the results are as expected, i think while passing results(data) from sql to qlik the anomaly is coming.

e.g. i think the sql is passing the value as 30, however qlik is taking it as 30.0, now even if i apply text function(to the value 30.0) it will remain same. Please be noted the value i am passing from sql is string. If i embed spaces to the value 30, qlik is taking it as 30.0(with no spaces), but if i embed '*' or any other character qlik is taking it as *30 which is correct.

Thanks

[dplr-rn]

When you load the column in question lets say its called ABC

if(isnum(ABC),ABC,0) as ABC //or as a new column

basically remove the characters and replace by 0

[nitin_2409]

Hi Dilip

the values coming in a single column (say ABC) are like:

30

30.1

30.123

NA

NM

if I use the below formula:

if(isnum(ABC),round(ABC,1/pow(10,precision)),ABC)

where precision is the number of digits after decimal

then it works perfectly fine, however I don't have a column to define precision for each row values.

so basically values within a column can be number (with unique precision) and text.

let me know if there is some other way to get it done, because I want to maintain different precisions within a single column.

Nitin

[dplr-rn]

What do you mean by 'however I don't have a column to define precision for each row values.'

are all values going to have different precisions? if its defined by a separate column that should be ok right?

slightly confused by the requirement. maybe give a table of source data and desired output data and i can try and help

[nitin_2409]

Hi Dilip,

yes, the values in this column are of different type with different precision. It can even have the values in percentage(e.g 30% or 30.23%) or string (e.g. like 'NM' or 'NA' or 'ABCD' or '-') or simply NULL.

Hence the column used to held these values is of string type in database. I am just fetching that column into qlik.

Even if i am able to find the precision depending upon the values in other column, i wont be able to cover all the scenarios.

e.g i was using this formula (and suppose there is a precision column)

if(isnum(ABC),round(ABC,1/pow(10,precision)),ABC)

Now 1 of the values in this column (ABC) is 1,234.67, after using this formula the value coming is 1234.67(it is omitting the comma separator)

Also if the value is in percentage like 34.23%  then after applying this formula, i get the values as 0.34.

My point being there can be so many combinations (or rather types) of value in this column, and there will be good chances if some scenario is left out.

I only want that the exact datatype should get passed to qlik, so if the datatype of column is string then string should be passed or if it is number then number should be passed.

Till now i have only seen issue with whole numbers, e.g 30 shown as 30.0, or 30.0 shown as 30. The issue is highly intermittent.

Let me know in case any other information is needed.

I appreciate your interest in my query.

Thank you

Nitin

[dplr-rn]

did you try text() function?

load text(Text) as Text inline [

Text

30

30.1

30.123

NA

NM

];