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     [SOLVED] Multiple Variable Masks in a flat file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Multiple Variable Masks in a flat file
 Login/Join
 
Member
posted
I have a FOCUS flat file that contains multiple lists of masks that can be used to extract fields from another file.
How can I extract the information correctly if the masks in the Mask File are similar to this:
Mask1 Mask2 Mask3
66$$$$ 13$$$$ (blank)
66$$$$ 03$$$$ (blank)
66$$$$ (blank) C
Addresses of Parts extracted from the main file must start with
66 and 13
or 66 and 03
or 66 and (anything) and 'C'

Users can change the masks in the Mask File at anytime, including adding/changing the mask from four $ in the A6 field to one or three $ in the A6 field.

The masks can be of various lengths based on the number of $ in the mask but all A6.

I have had limited success using:
WHERE fieldname1 EQ (holdfilename1)
WHERE fieldname2 EQ (holdfilename2)
or
WHERE fieldname1 IN FILE holdfilename1
WHERE fieldname2 IN FILE holdfilename2

But it keeps ANDing the results, I need to OR the results which this type of WHERE statement cannot do in a compound way, i.e. I need this to happen but get an error:
WHERE (fieldname1 EQ (holdfilename1)) OR (fieldname2 EQ (holdfilename2))

or

WHERE (fieldname1 IN FILE holdfilename1) OR (fieldname2 IN FILE holdfilename2)

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


Robert B. Clark -- Business Systems Analyst
Daimler Trucks North America
Phone: 503.745.7057
"There's always a better way."
IBM Mainframe zOS 8.1.05, DB2 adapter
WF 8.1.05 Hub to Sub, Self-Service, SQL Server adapter
 
Posts: 25 | Location: Portland, OR | Registered: October 13, 2006Report This Post
Expert
posted Hide Post
If I understand your requirements correctly, you can't do WHERE... IN FILE...

But the WHERE ... OR ... should work.

What error do you get?

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
I can do this WHERE function:
WHERE fieldname1 EQ (holdfilename1)
WHERE fieldname2 EQ (holdfilename2)

and I can do this WHERE function:
WHERE fieldname1 IN FILE holdfilename1
WHERE fieldname2 IN FILE holdfilename2
But that is the equivalent of ANDing the two WHERE statements together.
What I need to do would be the equivalent of ORing the two WHERE statements. Like this:
WHERE (fieldname1 EQ (holdfilename1)) OR (fieldname2 EQ (holdfilename2))

or using the WHERE function like this:
WHERE (fieldname1 IN FILE holdfilename1) OR (fieldname2 IN FILE holdfilename2)

But I get this error:
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: holdfilename1


Robert B. Clark -- Business Systems Analyst
Daimler Trucks North America
Phone: 503.745.7057
"There's always a better way."
IBM Mainframe zOS 8.1.05, DB2 adapter
WF 8.1.05 Hub to Sub, Self-Service, SQL Server adapter
 
Posts: 25 | Location: Portland, OR | Registered: October 13, 2006Report This Post
Expert
posted Hide Post
Although I may have got the logic behind the masks and fields, you could try this concept.
EX -LINES 6 EDAPUT MASTER,msk_dat,CV,FILE
FILENAME=MSK_DAT, SUFFIX=FIX,$
SEGNAME=MSK_DAT, $
  FIELD=MASK1 ,ALIAS=  ,A7 ,A8 ,$
  FIELD=MASK2 ,ALIAS=  ,A7 ,A8 ,$
  FIELD=MASK3 ,ALIAS=  ,A7 ,A8 ,$


EX -LINES 4 EDAPUT FOCTEMP,msk_dat,CV,FILE
EN%     JA%     
EN%     JE%     
EN%             TR7

-* or you could use
-*EN_____ JA____  
-*EN_____ JE____  
-*EN_____         TR7

-RUN

FILEDEF MSK_DAT DISK msk_dat.ftm (LRECL 24

DEFINE  FILE MSK_DAT
 CNTR/I1 WITH MASK1 = LAST CNTR + 1 ;
 ACNT/A1  = EDIT(CNTR) ;
 MSK1/A26 = IF MASK1 NE ' ' THEN 'FieldName1 LIKE ''' || MASK1 || ''''  ELSE ' ' ;
 CND1/A3  = IF MSK1 NE ' ' THEN 'AND' ELSE ' ' ;
 MSK2/A56 = IF MASK2 EQ ' ' THEN MSK1 ELSE MSK1 | CND1 | ' FieldName2 LIKE ''' || MASK2 || '''' ;
 CND2/A3  = IF MSK2 NE ' ' THEN 'AND' ELSE ' ' ;
 MSK3/A86 = IF MASK3 EQ ' ' THEN MSK2 ELSE MSK2 | CND2 | ' FieldName3 LIKE ''' || MASK3 || '''' ;

 WHERE_TEXT/A6   = IF CNTR EQ 1 THEN 'WHERE' ELSE 'OR' ;
 WHERE_LINE/A100 = WHERE_TEXT | MSK3 ;
END

TABLE   FILE MSK_DAT
 PRINT  
        WHERE_LINE
 ON     TABLE SAVE AS WHR_COND
END

-RUN

DEFINE FILE CAR
 FieldName1/A10 = COUNTRY ;
 FieldName2/A16 = CAR ;
 FieldName3/A24 = MODEL ;
END

TABLE FILE CAR
PRINT FieldName1
      FieldName2
      FieldName3
-INCLUDE WHR_COND
END


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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
<JG>
posted
Just got to make sure that the expression is correctly compounded

WHERE ((field IN FILE file1) OR (field IN FILE file2))

The following works no problem

FILEDEF F1 DISK f1.ftm
FILEDEF F2 DISK f2.ftm
FILEDEF F3 DISK f3.ftm
-RUN
-*
-WRITE F1 EN$*
-WRITE F2 F$*
-WRITE F3 JA$*
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE ((COUNTRY IN FILE F1) OR (COUNTRY IN FILE F2));
END
-RUN

Get the braces right and the worlds the limit

TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE (((COUNTRY IN FILE F1) OR (COUNTRY IN FILE F2)) AND (NOT CAR IN FILE F3));
END
-RUN
 
Report This Post
Virtuoso
posted Hide Post
One can also use the old form (fewer keystrokes...):
  
TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE ((COUNTRY EQ (F1)) OR (COUNTRY EQ (F2)));
END
-RUN
TABLE FILE CAR
PRINT
COUNTRY MODEL
WHERE (((COUNTRY EQ (F1)) OR (COUNTRY EQ (F2))) AND (CAR NE (F3)));
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
You need to test whether the three keys in a data record match the three respective masks in one of the rows of the user-maintained criterion file.

A series of file-tests, even connected with AND, won't work: It would select a data row if its three fields each matched some value in the three respective lists of mask values, even if the matches were not all from a single row in the original mask file.

Instead, construct the selection test as the comparison of a single field to a single file:

Define a single field to test, combining the three original data fields. Assuming they are each the same length, 6 characters, as the mask values in the illustration, just catenate them:
TESTFIELD/A18= FLD1 | FLD2 | FLD3 ;

Generate a flat file containing the user-supplied masks in corresponding form -- padded to the designated length with $'s, and catenated so they are column-aligned.

The code below illustrates the idea.

For clarity, I threw in a blank to separate the three fields and the three masks; and used LIST with IF TOTAL to identify source of the rows in the result.

I have not shown how to transform the user-supplied rows of mask values into the required form. You'll have to work that out -- either in Dialog Manager with a repeat loop, or with Define and Table operating (with a appropriate MFD) on the original file of mask values.
FILEDEF MASKS DISK MASKS.TXT
FILEDEF TESTDATA DISK TESTDATA.TXT
FILEDEF MFD      DISK TESTDATA.MAS
-RUN
-WRITE MASKS 55$$$$ 03$$$$ $$$$$$
-WRITE MASKS 66$$$$ 13$$$$ $$$$$$
-WRITE MASKS 77$$$$ $$$$$$ C$$$$$

-WRITE TESTDATA 55XXXX13YYYYCZZZZZ
-WRITE TESTDATA 55XXXX03YYYYZZZZZZ
-WRITE TESTDATA 66XXXX13YYYYZZZZZZ
-WRITE TESTDATA 77XXXXYYYYYYCZZZZZ
-WRITE TESTDATA ABCDEFGHIJKLMNOPQR

-WRITE MFD FILENAME=TESTDATA,SUFFIX=FIX
-WRITE MFD  SEGNAME=TESTDATA,SEGTYPE=S0
-WRITE MFD    FIELD=DATA1,DATA1,A6,$
-WRITE MFD    FIELD=DATA2,DATA2,A6,$
-WRITE MFD    FIELD=DATA3,DATA3,A6,$
-RUN
DEFINE FILE TESTDATA
TESTDATA/A20=DATA1 |' '| DATA2 |' '| DATA3;
END
TABLE FILE TESTDATA
LIST TESTDATA 
IF TOTAL TESTDATA EQ (MASKS)
END

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Jack,
You were correct on the ANDing. Your method of concatenating the fields worked.
Ready for part 2:
Now that I have the correct data filtered by the TESTFIELD, in the MASK file there is a Modifier field called MOD. Each of the newly filtered results records needs to be updated based on the MODifier of the set of Masks.
For "regular" data I would use a JOIN to join the two files together, allowing the "connection" of the new MODifier and update the old modifier, called LDTMMOD.
But, you cannot join a mask to real data. How do I pass the new MODifier to the new filtered results without a JOIN???


Robert B. Clark -- Business Systems Analyst
Daimler Trucks North America
Phone: 503.745.7057
"There's always a better way."
IBM Mainframe zOS 8.1.05, DB2 adapter
WF 8.1.05 Hub to Sub, Self-Service, SQL Server adapter
 
Posts: 25 | Location: Portland, OR | Registered: October 13, 2006Report This Post
Virtuoso
posted Hide Post
Oh ho -- so you need to know WHICH row of the mask-collection is matched. That's quite different from "IS THERE a row that matches".

The CHKFMT() function can be used, with the same 'combined mask values', to check each data record's key against one row of the mask collection.

Unfortunately, the mask argument of CHKFMT cannot be coded as the name of a variable -- you need to generate code that explicitly gives each mask as a quoted literal.

Using either DM or Define, generate a nested-IF test to look for the first matching mask-set and assign the corresponding Mod value -- something like the following (the lines between the comments are what needs to be generated dynamically, one line per row in the mask collection):
DEFINE FILE records
  COMBINED/A18=FLD1 | FLD2 | FLD3;
  MODVALUE/A20=
-* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       IF CHKFMT(18, COMBINED, '66$$$$03$$$$$$$$$$','I3') EQ 0 THEN 'AAAAAAAAAAAAAAAAAAAA'
  ELSE IF CHKFMT(18, COMBINED, '66$$$$13$$$$$$$$$$','I3') EQ 0 THEN 'BBBBBBBBBBBBBBBBBBBB'
  ELSE IF CHKFMT(18, COMBINED, '66$$$$$$$$$$C$$$$$','I3') EQ 0 THEN 'CCCCCCCCCCCCCCCCCCCC'
-* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  ELSE ' ';
END
TABLE FILE records
  PRINT FLD1 FLD2 FLD3 etc MODVALUE
  WHERE MODVALUE NE ' ';
  ON TABLE HOLD
END

The HOLD file can then be fed to a Modify or Maintain, to update the data source.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Jack,
Thank you. I was able to use your code with the concatenated Mask field:
AND COMPUTE TEST1/A13 = SA1 | SA2 | NEWABC;
against the inventory file's new concatenated field:
TEST2/A13 = TMPSA1 | TMPSA2 | TMPDUCATCD;
Along with code from Walter @ IBI:
ON TABLE HOLD AS HMOD FORMAT ALPHA
-READ HMOD NOCLOSE &TEST1.A13. &MODP.A3. &MOD.A3. &REST.A13.
-IF &TEST1 EQ ' ' GOTO SKIPX;
WHERE TEST2 EQ '&TEST1'
-SKIPX
ON TABLE HOLD AS TRANOUT FORMAT ALPHA
END
-RUN
-READREC
-READ HMOD NOCLOSE &TEST1.A13. &MODP.A3. &MOD.A3. &REST.A13.
-IF &IORETURN NE 0 GOTO RDDONE;
DYNAM COPY TRANOUTX TRANOUT APPEND
-RUN
-GOTO READREC
-RDDONE
TABLE FILE TRANOUT
PRINT *
END

And FINALLY received the results I needed for this MASKing MODifier project.
They need to upgrade you from Master to ...

Thanks again.
-robert-


Robert B. Clark -- Business Systems Analyst
Daimler Trucks North America
Phone: 503.745.7057
"There's always a better way."
IBM Mainframe zOS 8.1.05, DB2 adapter
WF 8.1.05 Hub to Sub, Self-Service, SQL Server adapter
 
Posts: 25 | Location: Portland, OR | Registered: October 13, 2006Report This Post
Virtuoso
posted Hide Post
I prefer a solution based solely on Table processing, to one that processes the lines of a file via a -READ loop.

The code below uses the Movies file (in apps/ibisamp). I used Category, Studio (part of Moviecode) and Rating as the three criterion columns. &MASK1 thru -3 represent the user-maintained control data (the three criteria plus an Action column). The code produces a PDF report listing the matching movies and the assigned action (the Mask data appear in the page footing).
-**** setup:
-SET &MASK1='CL,   ,G  ,BUY ,$';
-SET &MASK2='SC,   ,R  ,HOLD,$';
-SET &MASK3='MU,CBS,   ,SELL,$';
FILEDEF MASKS DISK MASKS.TXT
FILEDEF MFD   DISK MASKS.MAS
-RUN
-WRITE MFD FILENAME=MASKS,SUFFIX=COM
-WRITE MFD SEGMENT=MASK1,SEGTYPE=S0
-WRITE MFD FIELD=CAT,,A8,$
-WRITE MFD FIELD=STU,,A3,$
-WRITE MFD FIELD=RTG,,A4,$
-WRITE MFD FIELD=ACT,,A4,$
-WRITE MASKS &MASK1
-WRITE MASKS &MASK2
-WRITE MASKS &MASK3

-**** process:
DEFINE FILE MASKS
  _BLANK /I3=BYTVAL(' X','I3');
  _DOLLAR/I3=BYTVAL('$X','I3');
  CAT/A8 = CTRAN(8,CAT,_BLANK,_DOLLAR,CAT);
  STU/A3 = CTRAN(3,STU,_BLANK,_DOLLAR,STU);
  RTG/A4 = CTRAN(4,RTG,_BLANK,_DOLLAR,RTG);
  MASK/A17= CAT | '.' | STU | '.' | RTG ;
  LIST/I3=LIST+1;
  _ELSE_ /A6=IF LIST EQ 1 THEN ' ' ELSE ' ELSE ';
  LINE/A80= _ELSE_ | 'IF (CHKFMT(15,COMBINED,''' | MASK | ''',''I3'') EQ 0) THEN ''' | ACT || '''';
END
FILEDEF $TEMP DISK $TEMP.FEX
TABLEF FILE MASKS
  PRINT LINE
  ON TABLE SAVE AS $TEMP
END
-RUN
DEFINE FILE MOVIES
  STUDIO/A3=EDIT(MOVIECODE,'$$$999');
  COMBINED/A17 = EDIT(CATEGORY,'99999999') | '.' | STUDIO | '.' | EDIT(RATING,'9999');
  ACTION/A4=
-*************
-INCLUDE $TEMP
-*************
  ELSE ' ';
END
TABLE FILE MOVIES
  PRINT CATEGORY MOVIECODE RATING TITLE
    BY ACTION UNDER-LINE
    BY COMBINED
  IF ACTION NE ' '
  FOOTING BOTTOM
"MASKS:"
"  &MASK1"
"  &MASK2"
"  &MASK3"
  ON TABLE PCHOLD FORMAT PDF
  ON TABLE SET STYLE *
    TYPE=REPORT,
      ORIENTATION=LANDSCAPE,
      FONT=COURIER,
      SIZE=10,
    $
  ENDSTYLE
END


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
On second thought ... there is a potential problem is using CHKFMT with the user-supplied masks:

In addition to $, the characters 9, A, and X have special meanings in the mask argument of CHKFMT (matching any numeric, alphabetic, or alphanumeric character, resp.) -- whereas in the mask values of
IF (or WHERE) var EQ 'mask'
they just denote themselves.

So you can get false matches, using CHKFMT, if those characters happen to appear in the user-suppkied mask values.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 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] Multiple Variable Masks in a flat file

Copyright © 1996-2020 Information Builders