 |
|||||
|
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 |
| excel - integer field to alpha | Login/Join |
| Platinum Member |
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? webfocus 8.105M; os: windows; pdf, html, exl2k, csv | ||
|
| Virtuoso |
I would say look at your excel sort options, now you are dealing with character data not numeric data and character does not sort the way as numbers. Leah | |||
|
| Platinum Member |
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? webfocus 8.105M; os: windows; pdf, html, exl2k, csv | |||
|
| Virtuoso |
Have you tried sorting BY HIGHEST FIELD OR BY LOWEST FIELD?
| |||||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
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. webfocus 8.105M; os: windows; pdf, html, exl2k, csv | |||
|
| Virtuoso |
What if you took the field after you made it alpha again...right justified it. FIELD2/A4=LJUST(4,FIELD1,FIELD2);
| |||||
|
| Platinum Member |
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? webfocus 8.105M; os: windows; pdf, html, exl2k, csv | |||
|
| Virtuoso |
Will this work: REASON/A4 = EDIT(REASON_ID); REASONA/A4 = IF REASON EQ '0000' THEN ' ' ELSE REASON; Leah | |||
|
| Expert |
Pam, A throw away suggestion but try changing the USAGE format in your master to A4? T
| |||||||||
|
| Virtuoso |
Pam, 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
| |||||
|
| Platinum Member |
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. Thanks for all the suggestions so far. webfocus 8.105M; os: windows; pdf, html, exl2k, csv | |||
|
| Virtuoso |
According to the book the format field in the second position has to be enclosed in parenthesis, that is FTOA (REASON_ID,('A4'),REASON2); Just had to do it is why current in my mind. Leah | |||
|
| Platinum Member |
what I decided to do ... combo of suggestions I changed the master to be I4 per Mickey. I did per Leah's suggestion REASON1/A4=EDIT(REASON_ID); REASONID/A4=IF REASON1 EQ '0000' THEN ' ' ELSE REASON1; It now sorts. Zeroes appear in the numbers that they may not like (i.e. 0024 instead of 24) but it's at least sorting for them. Thanks. webfocus 8.105M; os: windows; pdf, html, exl2k, csv | |||
|
| Platinum Member |
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.  webfocus 8.105M; os: windows; pdf, html, exl2k, csv | |||
|
| Master |
After reading this thread, if I understand the problem, what you want is a number displayed as alpha without leading zeros? If so, this should work. Your master is: FIELD=REASON_ID,,I4,I4,$ Add this to your master DEFINE DREASON_ID/D4=REASON_ID; DEFINE AREASON_ID/A4=EDIT(FTOA(DREASON_ID,'(D4)','A5'),'9$999'); (note: on the 2nd parm, the parenthesis go inside the quote marks) REASON_IDs that are MISSING or zero, should display as blank in excel. In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux. | |||
|
| 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 |