Focal Point Banner


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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     excel - integer field to alpha

Read-Only Read-Only Topic
Go
Search
Notify
Tools
excel - integer field to alpha
 Login/Join
 
Platinum Member
posted
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
 
Posts: 179 | Registered: November 10, 2004Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 179 | Registered: November 10, 2004Report This Post
Virtuoso
posted Hide Post
Have you tried sorting BY HIGHEST FIELD OR BY LOWEST FIELD?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
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, 2004Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 179 | Registered: November 10, 2004Report This Post
Virtuoso
posted Hide Post
What if you took the field after you made it alpha again...right justified it.

FIELD2/A4=LJUST(4,FIELD1,FIELD2);


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 179 | Registered: November 10, 2004Report This Post
Virtuoso
posted Hide Post
Will this work:

REASON/A4 = EDIT(REASON_ID);
REASONA/A4 = IF REASON EQ '0000' THEN ' ' ELSE REASON;


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
Pam,

A throw away suggestion but try changing the USAGE format in your master to A4?

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
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

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 179 | Registered: November 10, 2004Report This Post
Virtuoso
posted Hide Post
quote:
REASON2/A4=FTOA(REASON_ID,'A4',REASON2);
and then
REASON2/A4=FTOA(REASON_ID,'F4',REASON2);
with no luck.


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
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 179 | Registered: November 10, 2004Report This Post
Platinum Member
posted Hide Post
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. Smiler


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     excel - integer field to alpha

Copyright © 1996-2020 Information Builders