 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Nodata in Excel | Login/Join |
| Gold member |
Does anyone have a solution to setting NODATA in EXCEL (EXL2K is not an option yet). I'm getting 'NA' in all of the missing cells and I need it to just be a null value instead. | ||
|
| Expert |
yep i do this SET NODATA = '' no space between the quotes By default NODATA comes out as a dot SET NODATA='.' That's what you see when data are missing. But a dot in an excel sheet is a messy problem. (You indicated you had SET NODATA='NA' or something like that.) So for any output that will be excel, i use this command at the top of the fex. then i get empty cells, truly empty. Excel is happy with that. | |||
|
| Gold member |
You know, I've never been so mad at a program as I am at this one...SET NODATA='' seemingly worked yesterday, but now I'm getting NA in the empty cells even after setting nodata to the two tick marks. It is frustrating as all get out and I can't decide if someone re-configured my Excel version on me or not. Guess I'll keep trying things, but it does drive me crazy because I've done computes where I set it to '' or missing or whatever and it still won't come up as a plain ol' empty cell. | |||
|
| <monte2000> |
Speaking to Excel guru here at IBI...have you tried going into "Tools" on your Excel menu and un-checking "View Zero Values"? Just curious if you've tried this.... | ||
|
| Gold member |
Just tried it and still stuck with the NA in the field...Have debated changing the value to be zero when missing and seeing if that will work, but I have values in these that can be equal to zero so I'd probably cause myself more headaches doing that. Am going to see what happens in LOTUS format also. | |||
|
| Silver Member |
Also, make sure you're not just re-using a cached copy of the file. When you use a browser, it may see a recently cached copy of a request and use it, so the report doesn't actually get re-run and you just open up a file that has already be generated. | |||
|
| Gold member |
Just FYI, I did reset my missings to a zero and when it came up I was able to see the zeros and turn off zeros in options which took them away, but this still doesn't solve my issue because I can have legitimate zeros in the column anyway and I also need the blanks to truly reflect a null value and not a zero. | |||
|
| Expert |
Stan, preview your output in a text file...to see if SET NODATA='' is working. If your Text file is ok, then its not focus, its excel somehow. If your text file has NA in it, then try a new really small focexec, in a very controlled environment, and set your nodata to '' and see if you can make a file that's right. If you can, then perhaps go back to your original fex and see whats going on. | |||
|
| Gold member |
Susannah, It turns out it is Excel that is indeed the problem. Excel from 1997 doesn't know how to handle null values so it places an NA in there when you use the DIF format. It blows away if you try EXL97 and when I use the EXCEL format it doesn't recognize what is being passed a truly null, and places spaces in the cell to take up the space. We are going to have a macro written to handle the DIF file format, but now I'm trying to figure the best solution to cause that macro to be called...I'm told I can have a VBA program call the macro and Excel and find a File that is saved, but it really isn't that ideal of a solution. It is an interesting program otherwise in that it never touches a database, but simply parses out a flat file into user defined columns and at the same time eliminates headers, limits on user defined phrases and the like. Unfortunately the users take the parsed file and apparently run these macros that expect null value in the cells rather than spaces. Thanks again for everyone's suggestions. Stan | |||
|
| Gold member |
For the record, I found a workaround by setting all of the space in an empty cell equal to the Hex for low values '00' and in the DIF format this appears to be a null cell. Just wanted to add the solution in case someone searches on a similar topic in the future. Stan | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |