 |
|||||
|
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 |
| Different NODATA settings | Login/Join |
| Member |
I'm doing a JOIN and have several columns showing '.' for the missing data values. If I set NODATA, then all of the missing values become that value. I need one of the columns to show blanks and others show '0'. I know I can do it with HOLDing the data first and then manipulating the MISSING, but I was wandering if there is a technique to do it in one pass, without the HOLD. Thank you in advance for any inputs! | ||
|
| Virtuoso |
The problem you have, I believe, is that internally, before output, these 'missing' values are actually treated as blank or zero. It is differentiating between a real blank or zero and a 'missing' field treated as blank or zero. If a field could never be blank or zero, then you can test it against blank or zero in a COMPUTE and put in the value you want. Does that make sense? Alan. WF 7.705/8.007 | |||
|
| Member |
The thing is, Alan, there are no missing values in the original databases. It is after the JOIN, because I'm using SET ALL = ON, that the missing values appear. I tried re-DEFINE-ing with IF against MISSING, but that did not work. | |||
|
| Virtuoso |
That's what I was trying, in my own way, to say. Internally WF is treating the values as either blank or zero. Missing is not an option, the values are not MISSING as such, just 'not there', and you can only check against a blank for alpha or zero for numeric for these 'not there' values. Problem comes when values are really blank or zero as opposed to not there, which causes the major issue. Alan. WF 7.705/8.007 | |||
|
| Member |
Thank you Alan. The fields are all numeric (and unfortunately I DO need one of them to be blank and not 0). I did try checking against 0s FIELD2 = IF FIELD1 EQ 0 THEN ' ' ELSE FIELD1; but it still did not work - it still showed '.'s. Is there something I'm missing or it just cannot be done in one pass? Thank you! | |||
|
| Virtuoso |
I can't really tell from your code what the formats of your fields are, but you don't have any format specified in the above code. Assuming FIELD1 IS numeric, try this: DEFINE FILE filename FIELD2/D12=IF FIELD1 EQ MISSING THEN 0 ELSE FIELD1; FIELD3/D12S=IF FIELD1 EQ MISSING THEN 0 ELSE FIELD1; END If there is no joined-to value for field1, field2 should show up as 0, field3 should show up as blank (zero-supressed). Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Virtuoso |
I don't think this can be done in one pass then. You are trying to put an alpha (blank) into a numeric. Alan. WF 7.705/8.007 | |||
|
| Member |
Thank you, Darin, but that's the first thing I've tried and it did not work. It might be that I'll have to stick with the two pass (HOLD first) way. Thank you everyone for their suggestions and inputs. If someone does find/know a way, please tell, I'd be very much interested. | |||
|
| Virtuoso |
The problem you correctly identified. I think we just didn't go far enough. If you move these to a COMPUTE (post internal matrix) it will work. Here's an example: JOIN EMPDATA.PIN IN EMPDATA TO ALL TRAINING.PIN IN TRAIN2 AS JOIN1 SET ALL=PASS TABLE FILE EMPDATA PRINT LASTNAME AND FIRSTNAME AND COURSECODE AND EXPENSES COMPUTE FIELD2/D12=IF EXPENSES EQ MISSING THEN 0 ELSE EXPENSES; COMPUTE FIELD3/D12S=IF EXPENSES EQ MISSING THEN 0 ELSE EXPENSES; BY PIN WHERE EXPENSES GT 3000 END Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Member |
AWESOME!!! Thank you very much, Darin! That worked! I just had to re-DEFINE one of the fields to ALPHA, so I can do EQ '' instead of EQ '0'. Thank you for that and reminding me that COMPUTE is done POST!!! internal matrix! Alan did mention COMPUTE, but I kinda got stuck on the DEFINEs. Sorry, Alan! P.S. Darin, is there a way to give you a plus or something  ? Sorry, I'm new to the Forums. | |||
|
| 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 |