Hi!
I've been having a problem with zip codes showing up correctly on a dashboard map. After some investigation, this appears to be because Cognos is removing leading zeros from the zip code, resulting in odd behavior. For example, 01607 is a zip code in Worcester, MA. However, because Cognos is stripping the leading 0, the map highlights 1607, which is a city in Kazakhstan. 

Here is my .csv data:

Here is the same data when imported into Cognos:

As you can see, in the first screenshot Notepad++ shows the leading zeros for the final column, "Zip_trimmed". When the data is imported into Cognos, you see that there are no longer leading zeros. Additionally, if you look closely on the map you can see that there is nothing highlighted in Massachusetts and instead, 1607 is highlighted in Kazakhstan. 

Question: How do I prevent Cognos from removing leading zeros?

Thank you for your help

------------------------------
Austin Milner
------------------------------

#CognosAnalyticswithWatson

Thanks for investigating and posting your question. I've had this same issue and didn't spend time looking into it. I look forward to seeing a response.

------------------------------
Kindest regards,
Cindy Berry
UX Designer
WH Payer Provider
------------------------------

Original Message:
Sent: 02-04-2019 12:13 PM
From: Austin Milner
Subject: Cognos Dashboards Stripping Leading Zeros

Hi!
I've been having a problem with zip codes showing up correctly on a dashboard map. After some investigation, this appears to be because Cognos is removing leading zeros from the zip code, resulting in odd behavior. For example, 01607 is a zip code in Worcester, MA. However, because Cognos is stripping the leading 0, the map highlights 1607, which is a city in Kazakhstan. 

Here is my .csv data:

Here is the same data when imported into Cognos:

As you can see, in the first screenshot Notepad++ shows the leading zeros for the final column, "Zip_trimmed". When the data is imported into Cognos, you see that there are no longer leading zeros. Additionally, if you look closely on the map you can see that there is nothing highlighted in Massachusetts and instead, 1607 is highlighted in Kazakhstan. 

Question: How do I prevent Cognos from removing leading zeros?

Thank you for your help

------------------------------
Austin Milner
------------------------------

#CognosAnalyticswithWatson

Austin/Cindy,

I am able to reproduce the problem you are seeing. I will get back to you tomorrow.

------------------------------
KIAN GOH
------------------------------

Original Message:
Sent: 02-04-2019 12:13 PM
From: Austin Milner
Subject: Cognos Dashboards Stripping Leading Zeros

Hi!
I've been having a problem with zip codes showing up correctly on a dashboard map. After some investigation, this appears to be because Cognos is removing leading zeros from the zip code, resulting in odd behavior. For example, 01607 is a zip code in Worcester, MA. However, because Cognos is stripping the leading 0, the map highlights 1607, which is a city in Kazakhstan. 

Here is my .csv data:

Here is the same data when imported into Cognos:

As you can see, in the first screenshot Notepad++ shows the leading zeros for the final column, "Zip_trimmed". When the data is imported into Cognos, you see that there are no longer leading zeros. Additionally, if you look closely on the map you can see that there is nothing highlighted in Massachusetts and instead, 1607 is highlighted in Kazakhstan. 

Question: How do I prevent Cognos from removing leading zeros?

Thank you for your help

------------------------------
Austin Milner
------------------------------

#CognosAnalyticswithWatson

@Austin Milner @Cindy Berry

I logged a defect ​​ for this problem. 

If you are using CA 11.1.1, I have a workaround for you. In the attached Excel file, I explicitly use '01697 (with apostrophe mark in front of 01697), then the leading zero in the Zip Code column  is "preserved" as text format.  In this case, '01697' is showing up in Dashboard, map is rendering as expected using "01697" Zip Code. 

If you are to log a support call, mention defect 266455.

------------------------------
KIAN GOH
------------------------------

Original Message:
Sent: 02-04-2019 05:24 PM
From: KIAN GOH
Subject: Cognos Dashboards Stripping Leading Zeros

Austin/Cindy,

I am able to reproduce the problem you are seeing. I will get back to you tomorrow.

------------------------------
KIAN GOH

Original Message:
Sent: 02-04-2019 12:13 PM
From: Austin Milner
Subject: Cognos Dashboards Stripping Leading Zeros

Hi!
I've been having a problem with zip codes showing up correctly on a dashboard map. After some investigation, this appears to be because Cognos is removing leading zeros from the zip code, resulting in odd behavior. For example, 01607 is a zip code in Worcester, MA. However, because Cognos is stripping the leading 0, the map highlights 1607, which is a city in Kazakhstan. 

Here is my .csv data:

Here is the same data when imported into Cognos:

As you can see, in the first screenshot Notepad++ shows the leading zeros for the final column, "Zip_trimmed". When the data is imported into Cognos, you see that there are no longer leading zeros. Additionally, if you look closely on the map you can see that there is nothing highlighted in Massachusetts and instead, 1607 is highlighted in Kazakhstan. 

Question: How do I prevent Cognos from removing leading zeros?

Thank you for your help

------------------------------
Austin Milner
------------------------------

#CognosAnalyticswithWatson

Thanks Kian. I'm using 11.0.12. I don't suppose the workaround works for that version? Anyway, good to know this will be fixed soon, my client will be happy to hear that

------------------------------
Austin Milner
------------------------------

Original Message:
Sent: 02-05-2019 04:39 PM
From: KIAN GOH
Subject: Cognos Dashboards Stripping Leading Zeros

@Austin Milner @Cindy Berry

I logged a defect ​​ for this problem. 

If you are using CA 11.1.1, I have a workaround for you. In the attached Excel file, I explicitly use '01697 (with apostrophe mark in front of 01697), then the leading zero in the Zip Code column  is "preserved" as text format.  In this case, '01697' is showing up in Dashboard, map is rendering as expected using "01697" Zip Code. 

If you are to log a support call, mention defect 266455.

------------------------------
KIAN GOH

Original Message:
Sent: 02-04-2019 05:24 PM
From: KIAN GOH
Subject: Cognos Dashboards Stripping Leading Zeros

Austin/Cindy,

I am able to reproduce the problem you are seeing. I will get back to you tomorrow.

------------------------------
KIAN GOH

Original Message:
Sent: 02-04-2019 12:13 PM
From: Austin Milner
Subject: Cognos Dashboards Stripping Leading Zeros

Hi!
I've been having a problem with zip codes showing up correctly on a dashboard map. After some investigation, this appears to be because Cognos is removing leading zeros from the zip code, resulting in odd behavior. For example, 01607 is a zip code in Worcester, MA. However, because Cognos is stripping the leading 0, the map highlights 1607, which is a city in Kazakhstan. 

Here is my .csv data:

Here is the same data when imported into Cognos:

As you can see, in the first screenshot Notepad++ shows the leading zeros for the final column, "Zip_trimmed". When the data is imported into Cognos, you see that there are no longer leading zeros. Additionally, if you look closely on the map you can see that there is nothing highlighted in Massachusetts and instead, 1607 is highlighted in Kazakhstan. 

Question: How do I prevent Cognos from removing leading zeros?

Thank you for your help

------------------------------
Austin Milner
------------------------------

#CognosAnalyticswithWatson

@Austin Milner

@Rob Hatfield shared a workaround:  The current data import UI does not have a way to specify the data type.  Since the column is all numeric, it is interpreted as a number, stripping the 0.  Excel will do the exact same thing unless you specify the column is text on import.  The easy work-around is to use xlsx and to specify the cell data type is text. 

I tried the workaround in 11.0.12, and it works for me. Attached the sample Excel file here. Hope that helps.

Cheers.​​

------------------------------
KIAN GOH
------------------------------

Original Message:
Sent: 02-05-2019 04:45 PM
From: Austin Milner
Subject: Cognos Dashboards Stripping Leading Zeros

Thanks Kian. I'm using 11.0.12. I don't suppose the workaround works for that version? Anyway, good to know this will be fixed soon, my client will be happy to hear that

------------------------------
Austin Milner

Original Message:
Sent: 02-05-2019 04:39 PM
From: KIAN GOH
Subject: Cognos Dashboards Stripping Leading Zeros

@Austin Milner @Cindy Berry

I logged a defect ​​ for this problem. 

If you are using CA 11.1.1, I have a workaround for you. In the attached Excel file, I explicitly use '01697 (with apostrophe mark in front of 01697), then the leading zero in the Zip Code column  is "preserved" as text format.  In this case, '01697' is showing up in Dashboard, map is rendering as expected using "01697" Zip Code. 

If you are to log a support call, mention defect 266455.

------------------------------
KIAN GOH

Original Message:
Sent: 02-04-2019 05:24 PM
From: KIAN GOH
Subject: Cognos Dashboards Stripping Leading Zeros

Austin/Cindy,

I am able to reproduce the problem you are seeing. I will get back to you tomorrow.

------------------------------
KIAN GOH

Original Message:
Sent: 02-04-2019 12:13 PM
From: Austin Milner
Subject: Cognos Dashboards Stripping Leading Zeros

Hi!
I've been having a problem with zip codes showing up correctly on a dashboard map. After some investigation, this appears to be because Cognos is removing leading zeros from the zip code, resulting in odd behavior. For example, 01607 is a zip code in Worcester, MA. However, because Cognos is stripping the leading 0, the map highlights 1607, which is a city in Kazakhstan. 

Here is my .csv data:

Here is the same data when imported into Cognos:

As you can see, in the first screenshot Notepad++ shows the leading zeros for the final column, "Zip_trimmed". When the data is imported into Cognos, you see that there are no longer leading zeros. Additionally, if you look closely on the map you can see that there is nothing highlighted in Massachusetts and instead, 1607 is highlighted in Kazakhstan. 

Question: How do I prevent Cognos from removing leading zeros?

Thank you for your help

------------------------------
Austin Milner
------------------------------

#CognosAnalyticswithWatson