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,

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.

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?

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

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!

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

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.

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!

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"

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

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