Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     (RESOLVED)Remove null space from DFIX file
Go
New
Search
Notify
Tools
Reply
  
(RESOLVED)Remove null space from DFIX file
 Login/Join
 
Platinum Member
posted
Hi all I am looking for help with a DFIX file that we re preparing for a feed to a third party application the reqirements are quite rigid that they want a pipe delimited file with no spaces. I chose DFIX because attempting to do concatenations gave me leading or triling spaces. the DFIX works quite well except in regards to null values. when I have empty fields I have spaces between the pipes. | | Anyone have have any tips or insight as to how i could remove these? Confused

the code is faily complex so I will only show the last pass through the data. My hold files are default until thsi point which should be alpha.

-*************JOIN ALL TOGETHER AND ADD HONORS AND SPORT****************************************************************
JOIN CLEAR *
JOIN
LEFT_OUTER HOLDSUMMA2.HOLDSUMM.PIDM_KEY IN HOLDSUMMA2 TO UNIQUE
GOREMAL.GOREMAL.GOREMAL_PIDM IN GOREMAL TAG J20 AS J20
END
JOIN
LEFT_OUTER HOLDSUMMA2.HOLDSUMM.PIDM_KEY IN HOLDSUMMA2 TO UNIQUE
HOLDSPRT.HOLDSPRT.SGRSPRT_PIDM IN HOLDSPRT TAG J11 AS J11
END
JOIN
LEFT_OUTER HOLDSUMMA2.HOLDSUMM.PIDM_KEY IN HOLDSUMMA2 TO UNIQUE
HOLDHON.HOLDHON.RRRAREQ_PIDM IN HOLDHON TAG J13 AS J13
END
DEFINE FILE HOLDSUMMA2
BUILDING/A30=
IF HOLDSUMMA2.HOLDSUMM.DEF_BUILD EQ ' ' THEN 'Off Campus'
ELSE HOLDSUMMA2.HOLDSUMM.DEF_BUILD;
DUMMY/A4='N/A';
D_MONTH/M=DEFDOB;
DEF_MONTH_ALPHA/A2=EDIT(D_MONTH);
D_DAY/D=DEFDOB;
DEF_DAY_ALPHA/A2=EDIT(D_DAY);
D_YEAR/YY=DEFDOB;
DEF_YEAR_ALPHA/A4=EDIT(D_YEAR);
DEF_ALPHA_DATE/A10=DEF_YEAR_ALPHA || '-' ||DEF_MONTH_ALPHA || '-' ||DEF_DAY_ALPHA;
DEF_TODAY/YYMD=&YYMD;
D_MONTH_TODAY/M=DEF_TODAY;
DEF_MONTH_ALPHA1/A2=EDIT(D_MONTH_TODAY);
D_DAY_TODAY/D=DEF_TODAY;
DEF_DAY_ALPHA1/A2=EDIT(D_DAY_TODAY);
D_YEAR_TODAY/YY=DEF_TODAY;
DEF_YEAR_ALPHA1/A4=EDIT(D_YEAR_TODAY);
DEF_ALPHA_DATE1/A10=DEF_YEAR_ALPHA1 || '-' ||DEF_MONTH_ALPHA1 || '-' ||DEF_DAY_ALPHA1;
DEF_GEN/A6=DECODE HOLDSUMMA2.HOLDSUMM.GENDER( F Female M Male ELSE '' );
DEF_ATH/A12=IF J11.HOLDSPRT.SGRSPRT_ACTC_CODE EQ ' ' THEN 'Not Athlete' ELSE J11.HOLDSPRT.SGRSPRT_ACTC_CODE ;
DEF_HON/A24=IF J13.HOLDHON.RTVTREQ_SHORT_DESC EQ ' ' THEN 'Not Honors' ELSE J13.HOLDHON.RTVTREQ_SHORT_DESC ;
END
-*************FORMAT DISPLAY WITH HYPHENS IN DATES APPENS SYSDATE FOMRATTED WITH HYPHENS****************************************
TABLE FILE HOLDSUMMA2
PRINT
HOLDSUMMA2.HOLDSUMM.ID
HOLDSUMMA2.HOLDSUMM.GOBTPAC_EXTERNAL_USER
HOLDSUMMA2.HOLDSUMM.LASTN
HOLDSUMMA2.HOLDSUMM.FIRSTN
HOLDSUMMA2.HOLDSUMM.SPRIDEN_MI
HOLDSUMMA2.HOLDSUMM.DEF_ALPHA_DATE
HOLDSUMMA2.HOLDSUMM.DEF_GEN
HOLDSUMMA2.HOLDSUMM.ETHN_DESC
HOLDSUMMA2.HOLDSUMM.BUILDING
HOLDSUMMA2.HOLDSUMM.CK_BED_SPACE
HOLDSUMMA2.HOLDSUMM.ADDRESSCB
HOLDSUMMA2.HOLDSUMM.CITYCB
HOLDSUMMA2.HOLDSUMM.STATCB
HOLDSUMMA2.HOLDSUMM.ZIPCB
HOLDSUMMA2.HOLDSUMM.PHONELO
HOLDSUMMA2.HOLDSUMM.PHONECE
HOLDSUMMA2.HOLDSUMM.ADDRESSPR
HOLDSUMMA2.HOLDSUMM.CITYPR
HOLDSUMMA2.HOLDSUMM.STATPR
HOLDSUMMA2.HOLDSUMM.ZIPPR
HOLDSUMMA2.HOLDSUMM.PHONEPR
HOLDSUMMA2.HOLDSUMM.PHOMEM
J20.GOREMAL.GOREMAL_EMAIL_ADDRESS
HOLDSUMMA2.HOLDSUMM.ADVISOR
HOLDSUMMA2.HOLDSUMM.INST_PTGPA_GPA
HOLDSUMMA2.HOLDSUMM.INST_LGPA_GPA
DEF_ATH
DUMMY
DEF_HON
DUMMY
DEF_ALPHA_DATE1
BY HOLDSUMMA2.HOLDSUMM.ID NOPRINT
BY HIGHEST J20.GOREMAL.GOREMAL_ACTIVITY_DATE NOPRINT
WHERE ( J20.GOREMAL.GOREMAL_EMAL_CODE EQ 'UNIS' ) AND ( J20.GOREMAL.GOREMAL_STATUS_IND NE 'I' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD AS OUT1 FORMAT DFIX DELIMITER |
END

thanks

This message has been edited. Last edited by: Geoff Fish,


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
construct your entire record as one variable, pipes and all.
use STRIP command to make sure you haven't got anything you don't want.
FILEDEF your output as a .CSV file extension, and SAVE it, or HOLD FORMAT ALPHA




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Have you tried these?

SET HNODATA = ''
SET HOLDMISS = ON/OFF


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
ho! so much easier




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I gave it a spin to no avail

tried SET NODAT and SET HNODATA. Neither was effecive


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Here's one more to try in conjunction with HNODATA and HOLDMISS:

SET NULL = ON


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
so I should use all 3?
I have aboout 6 hold tables leading up to this last pass through the data

So I should use

SET NULL = ON
SET HNODATA = ON
AND HOLDMISS = ON?

I ALSO TRIED SET NODATA = ,$


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Yes, I suggest using the three together (with HOLDMISS=ON). If that doesn't work, the next step would be to ensure that all fields that may have missing values are defined with the MISSING=ON attribute (either in the master/synonym or in the DEFINE). Try it with a single field first. If it still doesn't work, then possible work-arounds might be susannah's suggestion, or a -READ/STRREP/-WRITE repeat loop following your DFIX HOLD to replace '| |' with '||'.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
It may just be a case of defining the field correctly using MISSING ON instead of setting the value to '' -

DEFINE FILE CAR
  NEW_DCOST/D12 MISSING ON = IF COUNTRY NE 'ENGLAND' THEN DCOST ELSE MISSING;
END

TABLE FILE CAR
  SUM RCOST
      COMPUTE NEW_RCOST/D12 MISSING ON = IF COUNTRY EQ 'ENGLAND' THEN RCOST ELSE MISSING;
      COMPUTE NEW_TEXT/A3 = IF COUNTRY EQ 'ENGLAND' THEN 'ENG' ELSE '';
      NEW_DCOST
      DCOST
   BY COUNTRY
   BY CAR
   BY MODEL
ON TABLE HOLD AS MYDFIX FORMAT DFIX DELIMITER |
END
-RUN

CMD TYPE mydfix.ftm

Works with either PCHOLD or HOLD output.

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, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
As a backup plan , you can build the pipe delimited line in a define, etc, and use PUTDDREC to write the line to a file, with the 4th parameter (String length) set using ARGLEN to remove the trailing spaces.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6337 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I changed form the DFIX to tab and concatenated the record into one big field this solved my issue.

Never discount brute force.

DEFINE FILE HOLDALL
DEMORECORD/A1200=
HOLDALL.HOLDALL.ID || '|' ||
HOLDALL.HOLDALL.GOBTPAC_EXTERNAL_USER || '|' ||
HOLDALL.HOLDALL.LASTN || '|' ||
HOLDALL.HOLDALL.FIRSTN || '|' ||
HOLDALL.HOLDALL.SPRIDEN_MI || '|' ||
HOLDALL.HOLDALL.DEF_ALPHA_DATE || '|' ||
HOLDALL.HOLDALL.DEF_GEN || '|' ||
HOLDALL.HOLDALL.ETHN_DESC || '|' ||
HOLDALL.HOLDALL.BUILDING || '|' ||
HOLDALL.HOLDALL.CK_BED_SPACE || '|' ||
HOLDALL.HOLDALL.ADDRESSCB || '|' ||
HOLDALL.HOLDALL.CITYCB || '|' ||
HOLDALL.HOLDALL.STATCB || '|' ||
HOLDALL.HOLDALL.ZIPCB || '|' ||
HOLDALL.HOLDALL.PHONELO || '|' ||
HOLDALL.HOLDALL.PHONECE || '|' ||
HOLDALL.HOLDALL.ADDRESSPR || '|' ||
HOLDALL.HOLDALL.ADDRESSPR || '|' ||
HOLDALL.HOLDALL.CITYPR || '|' ||
HOLDALL.HOLDALL.STATPR || '|' ||
HOLDALL.HOLDALL.ZIPPR || '|' ||
HOLDALL.HOLDALL.PHONEPR || '|' ||
HOLDALL.HOLDALL.PHOMEM || '|' ||
HOLDALL.HOLDALL.GOREMAL_EMAIL_ADDRESS || '|' ||
HOLDALL.HOLDALL.CLAS_DESC || '|' ||
HOLDALL.HOLDALL.MAJR_DESC1 || '|' ||
HOLDALL.HOLDALL.ADVISOR || '|' ||
HOLDALL.HOLDALL.A_PTGPA || '|' ||
HOLDALL.HOLDALL.A_LGPA || '|' ||
HOLDALL.HOLDALL.DEF_ATH || '|' ||
HOLDALL.HOLDALL.DUMMY || '|' ||
HOLDALL.HOLDALL.DEF_HON || '|' ||
HOLDALL.HOLDALL.DUMMY || '|' ||
HOLDALL.HOLDALL.DEF_ALPHA_DATE1;
END


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     (RESOLVED)Remove null space from DFIX file

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.