 |
|||||
|
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 |
| need to report trailing spaces | Login/Join |
| Gold member |
I have an issue where the database entry has trailing spaces making it 10 characters, but my report only shows 7 characters. For example: In the database is 'P123456 ' The report only shows 'P123456' My synonym shows the field is A20V. I have tried EXL2K and EXL07 and both remove the spaces. How can I get the spaces in the report? 8007 Windows 7, PDF, Excel | ||
|
| Expert |
change the USAGE in the .mas to /A10 leaving off the V and shortening the usable bits from 20 to 10 or... DEFINE a new field that is /A10 and use it.
| |||||
|
| Gold member |
I changed USAGE in .mas to A10 and still no trailing spaces. That said, it needs to remain 20 characters as that is the allowable input for that field. To give another example, a user may enter 'Fannie Mae ' and it does not exactly match our database entry of 'Fannie Mae'. This is a validation field and when users enter data that does not match specific fields in our database, it is reported here. There can be entries that take 20 characters, there may be entries that take only one character. There may be one mis keyed trailing space, there may be 5 or more trailing spaces. This is specifically to catch incorrect data entry. 8007 Windows 7, PDF, Excel | |||
|
| Expert |
you had said 10, above. and indicated that the problem lay in the excel export.that seems to be an issue with the way excel works. so now it seems your issue is with input, not output; if your user enters from an .htm page, then you have an &variable, yes? so you make sure to truncate that &var -SET &MYVAR = TRUNCATE(&MYVAR); to make sure you clean up what they've entered. and then use a LIKE in your filter statement -DEFAULT &MYBT = 'SEDAN '; -SET &MYBT = TRUNCATE(&MYBT) | '%'; TABLE FILE IBISAMP/CAR BY BODYTYPE BY MODEL ON TABLE SET HOLDLIST PRINTONLY IF BODYTYPE LIKE &MYBT.QUOTEDSTRING END
| |||||
|
| Gold member |
Sorry, I was just giving one example. What we have is a program where data entry from clients is uploaded each morning. Some of the data entry fields are validated against current loan data to verify there is actually a matching loan, servicer, investor, etc... If something doesnt match, it goes into an error table. The entry that failed is entered exactly how it was given by client. So if they give us data that has trailing spaces, it will fail because the database doesnt get an exact match because it takes the trailing spaces into account. The filed I need for the report is in an Oracle database. It is Data Type: VARCHAR2 (20 Byte). When I do a simple TABLE FILE table PRINT field in any format I do not get the trailing spaces in the example I have in my database. I have tried SQL Report and I do not get the trailing spaces. I select Sample Data from that field within a WebFocus synonym and I do not get the trailing spaces. I also tried to see if I could just get the length using ARGLEN function, and that does not count the spaces. Im wondering if WebFocus just does not report actual trailing spaces? If so I will need to contact IBI because that is incorrect data being returned. 8007 Windows 7, PDF, Excel | |||
|
| Expert |
Add another test to compare the ARGLEN of the field against the ARGLEN of the field soft concatenated with a non blank character - 1. If the lengths do not agree then you have trailing blank(s). DEFINE FILE something APPARENT_LEN/I2 = ARGLEN(20, field, 'I2'); REAL_LEN/I2 = ARGLEN(21, field | '#', 'I2') - 1; END T
| |||||||||
|
| Expert |
then you need to better manage your matching process. the concept of 'trailing spaces' in a /AnV field doesn't make sense.... they're mutually exclusive, by def. you need to understand that your browser is going to ignore trailing spaces, so you need to separate the concept of database from the concept of what you see in your browser. your issue has zero to do with wf.
| |||||
|
| Gold member |
I should get 10 (7 char + 3 spaces) I get this: APPARENT_LEN = 7 REAL_LEN = 20 (that is the size of the field) 8007 Windows 7, PDF, Excel | |||
|
| Gold member |
This is how we manage our matching process. We get data from clients, we must import data exactly as a liability. This validation report shows clients what they do wrong so we get more correct data files. 8007 Windows 7, PDF, Excel | |||
|
| Virtuoso |
Use LENV() to get the length "taken from the length-in-bytes of the field", and subtract the result of ARGLEN() to compute the number of trailing blanks stored in the field. - Jack Gross WF through 8.1.05 | |||
|
| Gold member |
When I change the USAGE back to A20V, I get 10 using LENV(). I also get 10 in REAL_LEN with Tony A example. So now I know it is passing the spaces, it's just not reporting them... 8007 Windows 7, PDF, Excel | |||
|
| Expert |
As a test I would append a character to the end of the column and run the report in PDF format. HTML reduces multiple blanks to one. The Excel format WF generates isn't really Excel so you can't be sure that your Excel file is behaving as expected. Or, perhaps, one of the esoteric Excel formats will work:
Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Virtuoso |
Use LENV() to get the length-of-value attribute. Subtracting ARGLEN() will yield the number of trailing blanks. - Jack Gross WF through 8.1.05 | |||
|
| Gold member |
Francis, I did try that and I do get one space in between the field and the character, COMPUTE P_CD/A22V = PARTY_CD | 'I'; Gives me P123456 I (one space) This still isnt a solution that will work for us. Also, all of our reports have to run with the option of HTML, PDF, or Excel. So the solution has to work for all in same fex. 8007 Windows 7, PDF, Excel | |||
|
| Expert |
For HTML reports, how do you envision showing trailing blanks when you cannot see them, or that they get collapsed to one? For testing purposes, did you test COMPUTE P_CD/A22V = PARTY_CD | 'I'; in PDF, where blank characters are not collapsed to one? Perhaps you can substitute trailing blanks for underscores or something. Or have a report column that specifies how many trailing blanks were found... Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Virtuoso |
If your objective is to emphasize trailing blanks, change them to something visible, like "^" or "°". - Jack Gross WF through 8.1.05 | |||
|
| Gold member |
So maybe not HTML, this scenario could be something we say has do be seen in only Excel, if I can get it to work... 8007 Windows 7, PDF, Excel | |||
|
| Expert |
So, how do you think you will depict trailing blanks when you cannot see them? Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Expert |
This fex reads an Oracle table that has one column containing data with a varying number of trailing blanks. TABLE FILE FM_TEST1 PRINT COLUMN1 COMPUTE COLUMN1X/A21V = COLUMN1 | 'x'; ON TABLE SET STYLEMODE FIXED -*ON TABLE PCHOLD FORMAT PDF END  Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Gold member |
What Im trying to achieve is the Excel export from WebFocus gives me the same output as Excel export from Oracle. I cannot visually see the trailing spaces when exporting directly from Oracle, but when I see a value that looks to be correct and click inside the cell exported from Oracle, I am able to right arrow past the characters to see there are spaces. In the WebFocus export to Excel, it does not include the spaces after characters that are in the database. What I dont understand is why in WebFocus, when I add this formula: COMPUTE ALEN/I2 = LENV(field, ALEN); It gives me 10, which is correct (7 characters + 3 spaces), but When i click throught that field in excel, it does not include the spaces. So the spaces are recognized, but not exported. 8007 Windows 7, PDF, Excel | |||
|
| Master |
Did you try with "SET SHOWBLANKS=ON" settings. Check below link. http://infocenter.informationb...ource%2Ftopic104.htm Thanks, Ram | |||
|
| Gold member |
This works for leading blanks, but not for trailing blanks. 8007 Windows 7, PDF, Excel | |||
|
| Master |
And the reason for why LENV displays correct length of 10 is WF will preserve internal blanks in all calculations and HOLD files. Only in HTML and EXL2k by default blanks are removed. | |||
|
| Expert |
It appears that for Excel, the trailing blanks are ignored even if if you append a character at the end of the trailing blanks... As Jack suggested, convert the blanks to some other character - either a visible character or perhaps you can find a special character that is rendered as a blank. Or, using Jack's suggestion re. LENV() and ARGLEN(), create a column showing a count of trailing blanks. Are you also concerned with multiple consecutive blanks that are not trailing? Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
hi Mikey, It may not help. But, just heads up.To keep the trailing blanks in excel, this is my first post in focal point, i guess. Take a look. Positioning datas within a CELL in EXCEL -Rifaz WebFOCUS 7.7.x and 8.x | |||
|
| Gold member |
This will add leading spaces, but thats not my issue. Also, we use WebFocus to display the report data, but use Oracle Procedures, Views and MVs to get the desired data. So I can easily turn those blanks into just about anything I want in Oracle before passing it to WF. Changing the spaces to anything else is not my issue. The issue is I need SET SHOWBLANKS = ON to work with trailing spaces. Maybe SET TRAILINGSPACES = ON will be added one day! 8007 Windows 7, PDF, Excel | |||
|
| Expert |
I think this was previously suggested, why not ensure that the triling blanks are truncated when the database is updated? An easy fix and then you don't need this error report. Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Platinum Member |
This seems to work: FILEDEF XLDATA DISK xldata.csv TABLE FILE ibisamp/car PRINT COUNTRY CAR MODEL SEATS COMPUTE COUNTRYV/A11V = COUNTRY; ON TABLE SAVE AS XLDATA FORMAT COMMA END SET HTMLFORMTYPE=CSV -RUN -HTMLFORM XLDATA Notes: The SET HTMLFORMTYPE=CSV may not be officially supported. Need to be configured to open .csv in Excel. Appears to work with Oracle varchars. Does not appear to work for alpha fields that contain all digits. Question: Why is this important? Any other reason than wanting to exactly mimic what your Oracle tool does? WebFOCUS 8.2.06 | |||
|
| 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 |