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 TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
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.
We have a field that used to be defined as A3. When the business sorted the field in excel, the blanks in the field all sorted to the top. The dba recently changed this field to an I4 (integer 4) field and now the blanks always stay at the bottom no matter how the column is sorted. I have tried creating a new field in the define and do an reasonid/a4=edit(reason_id) and also I tried a ftoa(reason_id,’i4’,reasonid) but the records continue to be at the bottom of the spreadsheet … what am I doing wrong??? I also tried REASONID/A4 MISSING ON=IF REASON_ID IS MISSING THEN ' ' ELSE EDIT(REASON_ID); and a variety of other options and nothing seems to be able to get the blank records to sort to the top. Any suggestions?
Hey Leah, I'm actually trying to get the excel to sort the field the way it used to without changing the database. The field used to be defined on the database as alpha(3) and the user loved how it sorted. It's now an integer(4) field and I'm trying to get the field to look and be the same as it was coded in A3 but I have to do it in the defines/computes to change it. So, I'm doing the edits, etc. to try to get it back to an alpha field the way it used to be an alpha field. So, in essence, excel would see it the same way as it used to. Am I making sense?
Okay, so in your report that is being exported to excel, what are you sorting on as Prarie indicates, you can use Highest or lowest. I would say that the issue is a numeric field using edit comes out as '0000' when it has a zero in it, so you'd have to replace those with spaces.
Leah
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004
I don't need to do the sorting within my code. I just need to get the format of the field to be the same as it used to be so when they sort it, it sorts correctly. They sort the spreadsheets all sorts of different ways so they want the initial sort by account id. They just want to be able to sort the report by reason id and it to sort like it used to as an alpha field.
new question ... in my master the field is defined as FIELD=REASON_ID ,REASON_ID, I11 ,I4 ,MISSING=ON ,$
Should I be using the I4 in my FTOA or I11?
I can get the edit command to work, but the zeroes display and the users do not want to see the zeroes. If I do the FTOA nothing displays in the field. I've tried REASON1/A11=EDIT(REASON_ID); REASONID/A4=FTOA(REASON_ID,'I4',REASONID); REASON2/A11=FTOA(REASON_ID,'I11',REASONID);
and various other combinations ... i.e. first putting my field to a D4 or D11 field and then using that in FTOA.
Is there another command besides FTOA that I should be using?
If all the values are only 4 digits long, change the USAGE format (I11) to match the ACTUAL format (I4).
FIELD=REASON_ID ,REASON_ID, I4 ,I4 ,MISSING=ON ,$
Your FTOA with the I4 should then show values. If it does not, change the I4 to F4 in the FTOA function and that should work.
I made this same dumb mistake a couple of weeks ago. When you have an I11 but you only specificy I4 in the function, the FTOA function grabs the first 4 positions of the RIGHT JUSTIFIED 11 digit number. In your case the 4 digit number has 7 blank spaces at the beginning of the number and therefore all you get back are the first 4 blank spaces.
Update the thread to let us know if this fixes the issue.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003
well, I wanted to started fresh today since I was having such bad luck testing yesterday (it never fails to have problems right before vacation).
Anyway, tried putting A4 per Tony's suggestion in my master in the first field and I got INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION FTOA which I had as REASON2/A4=FTOA(REASON_ID,'A4',REASON2); I tried A4 in the second field in my master and changed the first field back to I4 and I didn't error out but I got blanks for reason2.
Tried putting I4 in my master per Mickey and I still get blanks trying the below code. I tried REASON2/A4=FTOA(REASON_ID,'A4',REASON2); and then REASON2/A4=FTOA(REASON_ID,'F4',REASON2); with no luck.
If anyone else has a suggestion, I'm open to trying it. I may not respond as to the results for a week since I'm supposed to be on vacation.
I tried it with brackets (with the master as A4 and I4) and it's still giving me blank results. By the way, I also tried the FTOA with the obvious REASON3/A4=FTOA(REASON_ID,('I4'),REASON3); I believe all the ones I display above are with different values than I4 in the FTOA command. I haven't displayed everything I've tried.