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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     (SOLVED) How to keep leading zero's with a COMT output

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(SOLVED) How to keep leading zero's with a COMT output
 Login/Join
 
Gold member
posted
Hey all,
Just another formatting question!
I have a large data set that won't fit into an excel output, so in order to get all records returned, I need to use a COMT output. I need the last four digits of an I9 field, but a lot of the times those last four digits begin with a 0. For example, if the original integer is 4170062, then the last four need to read as 0062. When I output this into COMT, I am only getting 62.
I have tried computes and defines with no luck yet.
Does anyone have a good work around for this?
Thanks!
-Larissa

This message has been edited. Last edited by: LarissaB,


Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Report This Post
Expert
posted Hide Post
What are you using to get the last four digits? Is the result an 'I4'? If so, use the 'I4L' format instead of 'I4'. that should work.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
Hey Doug,
I was using the SUBSTR function, so the output was an 'A4'. I popped an EDIT() around the entire define and then changed the Format to I4L and I don't get any errors on the formula, but it still leaves off the leading zeros.
EDIT(SUBSTR(11, (EDIT(A_ACCT_ALL_MONTHLY.MEMBER.MBR_NUM)), 8, 11, 4, 'A4'))
I'm thinking the argument 6 within the function is overwriting the parent format of I4L, and I cannot change the 'A4' to I4L and check the define without errors.
Do you have a different way to grab the last 4 digits from an integer field?


Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Report This Post
Virtuoso
posted Hide Post
Try the DIGITS function:
  
DEFINE FILE CAR
SALES_A/A4=DIGITS(SALES,4);
END
TABLE FILE CAR
PRINT SALES SALES_A
BY COUNTRY
BY CAR
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Gold member
posted Hide Post
Thanks BabakNYC, I have never used the DIGITS function before. That is a more clear function to use than the SUBSTR, so I substituted my current SUBSTR define out for your DIGITS method.
The COMT output still strips those leading zeros however, even if I use the I4L format.

I'll still poke around with the two options that you guys gave me to see if I can come up with a solution.

I appreciate the ideas!


Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Report This Post
Virtuoso
posted Hide Post
There are lot of these simplified functions added in 8.2xx. Take a look here and look for Simplified Date, Numeric or Character functions. They are a lot easier to use.

https://webfocusinfocenter.inf...urce/func_opener.htm


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Master
posted Hide Post
Expanding on BabakNYC the following works for me:
  
DEFINE FILE CAR
SALES_A/I4L= TO_INTEGER(DIGITS(SALES,4));
END
TABLE FILE CAR
PRINT SALES SALES_A
BY COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT COMT
END
-RUN

I get the following output:
  
"COUNTRY","CAR","SALES","SALES_A"
"ENGLAND","JAGUAR",0,0000
"ENGLAND","JAGUAR",12000,2000
"ENGLAND","JENSEN",0,0000
"ENGLAND","TRIUMPH",0,0000
"FRANCE","PEUGEOT",0,0000
"ITALY","ALFA ROMEO",4800,4800
"ITALY","ALFA ROMEO",12400,2400
"ITALY","ALFA ROMEO",13000,3000
"ITALY","MASERATI",0,0000
"JAPAN","DATSUN",43000,3000
"JAPAN","TOYOTA",35030,5030
"W GERMANY","AUDI",7800,7800
"W GERMANY","BMW",8950,8950
"W GERMANY","BMW",8900,8900
"W GERMANY","BMW",14000,4000
"W GERMANY","BMW",18940,8940
"W GERMANY","BMW",14000,4000
"W GERMANY","BMW",15600,5600


One thing to remember, is that if you open a csv file in Excel (the default program in most windows systems) that Excel will strip off the leading zeros. If you open the csv with a text file editor, then the leading zeros are there like above.


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Gold member
posted Hide Post
Thank you Hallway, this worked!
Also, a great tip on remembering how data is rendered via opening files in txt or excel/csv. I need to get better at asking the third party who is receiving the data if they are able to extract the data from txt files where the leading zeros are clear or if they are pulling the data straight from the spreadsheet.

Thanks to all!


Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Report This Post
Platinum Member
posted Hide Post
Here's a way to look at what gets created by the "ON TABLE HOLD FORMAT COMT"

I added another SALES field (one is I4L and one is A4)

Here's the code ...

DEFINE FILE CAR
SALES_I4L/I4L = TO_INTEGER(DIGITS(SALES,4));
SALES_A4/A4   = DIGITS(SALES,4);
END
TABLE FILE CAR
PRINT SALES SALES_I4L SALES_A4
BY COUNTRY
BY CAR
ON TABLE HOLD FORMAT COMT
END
-RUN
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++  CMD DIR *.* (list all files in temp directory)
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CMD DIR *.*
-RUN
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++  CMD TYPE hold.mas
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CMD TYPE hold.mas
-RUN
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++  CMD TYPE hold.csv
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CMD TYPE hold.csv
-RUN


I get the following output ...

 0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 +++  CMD DIR *.* (list all files in temp directory)
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 Volume in drive D is Data
 Volume Serial Number is E8C6-BAB1
 Directory of D:\ProgramFiles\ibi\srv82\wfs\edatemp\ts000017
 10/12/2018  11:32 AM    <DIR>          .
 10/12/2018  11:32 AM    <DIR>          ..
 10/12/2018  11:32 AM               705 hold.csv
 10/12/2018  11:32 AM               365 hold.mas
 10/12/2018  09:54 AM                 0 stderr.log
 10/12/2018  09:54 AM                 0 stdout.log
 10/12/2018  11:32 AM                 0 sysh_1244.tmp
 5 File(s)          1,070 bytes
 2 Dir(s)   6,632,988,672 bytes free
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 +++  CMD TYPE hold.mas
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 FILENAME=HOLD    , SUFFIX=COMT    , $
 SEGMENT=HOLD, SEGTYPE=S2, $
 FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, ACTUAL=A10, $
 FIELDNAME=CAR, ALIAS=E02, USAGE=A16, ACTUAL=A16, $
 FIELDNAME=SALES, ALIAS=E03, USAGE=I6, ACTUAL=A06, $
 FIELDNAME=SALES_I4L, ALIAS=E04, USAGE=I4L, ACTUAL=A04, $
 FIELDNAME=SALES_A4, ALIAS=E05, USAGE=A4, ACTUAL=A04, $
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 +++  CMD TYPE hold.csv
 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 "COUNTRY","CAR","SALES","SALES_I4L","SALES_A4"
 "ENGLAND","JAGUAR",0,0000,"0000"
 "ENGLAND","JAGUAR",12000,2000,"2000"
 "ENGLAND","JENSEN",0,0000,"0000"
 "ENGLAND","TRIUMPH",0,0000,"0000"
 "FRANCE","PEUGEOT",0,0000,"0000"
 "ITALY","ALFA ROMEO",4800,4800,"4800"
 "ITALY","ALFA ROMEO",12400,2400,"2400"
 "ITALY","ALFA ROMEO",13000,3000,"3000"
 "ITALY","MASERATI",0,0000,"0000"
 "JAPAN","DATSUN",43000,3000,"3000"
 "JAPAN","TOYOTA",35030,5030,"5030"
 "W GERMANY","AUDI",7800,7800,"7800"
 "W GERMANY","BMW",8950,8950,"8950"
 "W GERMANY","BMW",8900,8900,"8900"
 "W GERMANY","BMW",14000,4000,"4000"
 "W GERMANY","BMW",18940,8940,"8940"
 "W GERMANY","BMW",14000,4000,"4000"
 "W GERMANY","BMW",15600,5600,"5600"



WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Expert
posted Hide Post
I do like that "TO_INTEGER(DIGITS(SALES,4))" function (New to me, thank Hallway). The good ol' EDIT (SALES_B) does it too just need to know the ACTUAL format of the field you're using. So many choices Smiler
DEFINE FILE CAR
SALES_A/I4L= TO_INTEGER(DIGITS(SALES,4));
SALES_B/I4L= EDIT(SALES,'$$9999');
END
TABLE FILE CAR
PRINT SALES SALES_A SALES_B
BY COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT COMT
END
-RUN
As seen in Excel:
COUNTRY       CAR         SALES  SALES_A  SALES_B
ENGLAND       JAGUAR          0        0        0
ENGLAND       JAGUAR      12000     2000     2000
ENGLAND       JENSEN          0        0        0
ENGLAND       TRIUMPH         0        0        0
FRANCE        PEUGEOT         0        0        0
ITALY         ALFA ROMEO   4800     4800     4800
ITALY         ALFA ROMEO  12400     2400     2400
ITALY         ALFA ROMEO  13000     3000     3000
ITALY         MASERATI        0        0        0
JAPAN         DATSUN      43000     3000     3000
JAPAN         TOYOTA      35030     5030     5030
W GERMANY     AUDI         7800     7800     7800
W GERMANY     BMW          8950     8950     8950
W GERMANY     BMW         8900      8900     8900
W GERMANY     BMW         14000     4000     4000
W GERMANY     BMW         18940     8940     8940
W GERMANY     BMW         14000     4000     4000
W GERMANY     BMW         15600     5600     5600
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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     (SOLVED) How to keep leading zero's with a COMT output

Copyright © 1996-2020 Information Builders