Hello dears

I upload one excel file into dashboard, there are two columns information are in number format in excel file, but when I upload the file, the 1st one column was identified as "text" format but it should be "number", the 2nd with no problem in number format.  Can you please kindly advise how can I change the format from "Text" to "Decimal" format? Thank you in advance!

------------------------------
Ya Jie Gao
------------------------------

#CognosAnalyticswithWatson

The best way to solve this problem is in Excel. I don't know which Cognos version you use, but Cognos should detect the right data type. This works correctly in 11.1.1. 

However, you can change the data type in the data module too (not recommended if it is not necessary).

To do this you can use the cast-decimal function and edit the expression into somewhat like this:

cast([Data item], decimal (6,2))​

After editing the expression Cognos will show the data type as decimal.

------------------------------
--
------------------------------

Original Message:
Sent: Fri September 20, 2019 02:01 AM
From: Ya Jie Gao
Subject: How to change "Data type" from text to decimal

Hello dears

I upload one excel file into dashboard, there are two columns information are in number format in excel file, but when I upload the file, the 1st one column was identified as "text" format but it should be "number", the 2nd with no problem in number format.  Can you please kindly advise how can I change the format from "Text" to "Decimal" format? Thank you in advance!

------------------------------
Ya Jie Gao
------------------------------

#CognosAnalyticswithWatson

Generally when the file is loaded and a column that appears numerical in excel is converted to text, the column has 'alpha' characters - could be spaces. The parser when it reads any non numeric, it creates the column in Cognos as text. 
One method to check the data in excel is the function ISNUMBER and ISBLANK. These will return TRUE as a result.
Create 3 columns that contain ColumnA (ISNUMBER), and ColumnB(ISBLANK) and one that is "=OR(ColumnA, ColumnB), if the third column return true your column is numeric. False would the problem column.

------------------------------
Michael McGeein
------------------------------

Original Message:
Sent: Fri September 20, 2019 02:01 AM
From: Ya Jie Gao
Subject: How to change "Data type" from text to decimal

Hello dears

I upload one excel file into dashboard, there are two columns information are in number format in excel file, but when I upload the file, the 1st one column was identified as "text" format but it should be "number", the 2nd with no problem in number format.  Can you please kindly advise how can I change the format from "Text" to "Decimal" format? Thank you in advance!

------------------------------
Ya Jie Gao
------------------------------

#CognosAnalyticswithWatson

Thank you so much Michael, I check the original value in excel file, it really contain some text format values.after I change it the dashboard recognize them as number value

------------------------------
Ya Jie Gao
------------------------------

Original Message:
Sent: Mon September 23, 2019 10:56 AM
From: Michael McGeein
Subject: How to change "Data type" from text to decimal

Generally when the file is loaded and a column that appears numerical in excel is converted to text, the column has 'alpha' characters - could be spaces. The parser when it reads any non numeric, it creates the column in Cognos as text. 
One method to check the data in excel is the function ISNUMBER and ISBLANK. These will return TRUE as a result.
Create 3 columns that contain ColumnA (ISNUMBER), and ColumnB(ISBLANK) and one that is "=OR(ColumnA, ColumnB), if the third column return true your column is numeric. False would the problem column.

------------------------------
Michael McGeein

Original Message:
Sent: Fri September 20, 2019 02:01 AM
From: Ya Jie Gao
Subject: How to change "Data type" from text to decimal

Hello dears

I upload one excel file into dashboard, there are two columns information are in number format in excel file, but when I upload the file, the 1st one column was identified as "text" format but it should be "number", the 2nd with no problem in number format.  Can you please kindly advise how can I change the format from "Text" to "Decimal" format? Thank you in advance!

------------------------------
Ya Jie Gao
------------------------------

#CognosAnalyticswithWatson

I have set one column as currency dollar but its coming as integer in data module.what should I do for this?

------------------------------
Priyanka Joshi
------------------------------

Original Message:
Sent: Mon September 23, 2019 10:56 AM
From: Michael McGeein
Subject: How to change "Data type" from text to decimal

Generally when the file is loaded and a column that appears numerical in excel is converted to text, the column has 'alpha' characters - could be spaces. The parser when it reads any non numeric, it creates the column in Cognos as text. 
One method to check the data in excel is the function ISNUMBER and ISBLANK. These will return TRUE as a result.
Create 3 columns that contain ColumnA (ISNUMBER), and ColumnB(ISBLANK) and one that is "=OR(ColumnA, ColumnB), if the third column return true your column is numeric. False would the problem column.

------------------------------
Michael McGeein

Original Message:
Sent: Fri September 20, 2019 02:01 AM
From: Ya Jie Gao
Subject: How to change "Data type" from text to decimal

Hello dears

I upload one excel file into dashboard, there are two columns information are in number format in excel file, but when I upload the file, the 1st one column was identified as "text" format but it should be "number", the 2nd with no problem in number format.  Can you please kindly advise how can I change the format from "Text" to "Decimal" format? Thank you in advance!

------------------------------
Ya Jie Gao
------------------------------

#CognosAnalyticswithWatson