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     Reading without MASTER file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Reading without MASTER file
 Login/Join
 
<Umar Farook S>
posted
Hi
Is there any way to READ data from external files excel or csv and use them inside webfocus without using master files.i.e. avoiding creationof master files
 
Report This Post
Virtuoso
posted Hide Post
No...

How does your system know what you are reading?
If you use an other language like .Net or VB you have to do that defines too.
So what do you expect? Magic?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
If they were flat or sequential, i.e. csv, you could use Dialog Manager to read them but you couldn't do a report. If that is your goal, you are out of luck.

Of course, with Dialog Manager you could also write a temporary master to read the file.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Master
posted Hide Post
Whenever I need read comma or delimited files from an outside source I use the following master. You can adjust to your record length.
FILENAME=hb1143data,SUFFIX=FIX, $
SEGNAME=SEG1 ,$
  FIELD=LDATA,,A160,A160, $


Then I use something like the following to "parse" the data.
 FILEDEF HB1143DATA DISK &TMPDIR..hb1143_0708.txt ( LRECL 160 RECFM V
-*
 DEFINE FILE HB1143DATA
   TAB/A1=HEXBYT(9,'A1');
-*
   WID/A4=GETTOK(LDATA,160,1,TAB,4,WID);
   ROWID/I4S=EDIT(WID);
-*
   F3LST/A3=GETTOK(LDATA,160,2,TAB,3,F3LST);
-*
   SSN/A9=GETTOK(LDATA,160,3,TAB,9,SSN);
-*
   ACASE1/A8=GETTOK(LDATA,160,4,TAB,8,ACASE1);
   CASE1/I8=EDIT(ACASE1);
-*
   ACASE2/A8=GETTOK(LDATA,160,5,TAB,8,ACASE2);
   CASE2/I8=EDIT(ACASE2);
-*
   ETHNIC/A3=GETTOK(LDATA,160,6,TAB,3,ETHNIC);
-*
   GNDR/A1=GETTOK(LDATA,160,7,TAB,1,GNDR);
-*
   WDOB/A10=GETTOK(LDATA,160,8,TAB,10,WDOB);
   ADOB/A8YYMD=EDIT(WDOB,'9999$99$99');
   CLDOB/YYMD=DATECVT(ADOB,'A8YYMD','YYMD');
-*
   MATCH_KEY/A13=
     IF F3LST NE ' ' AND GNDR NE ' ' AND ADOB NE ' '
       THEN F3LST | GNDR | ADOB ELSE ' ';
 END
-*
 TABLEF FILE HB1143DATA
  PRINT ROWID CASE1 CASE2 SSN MATCH_KEY
     IF ROWID GT 0
     ON TABLE HOLD
 END


In this case, the data was tab delimited, but you can do the same thing with comma delimited data.


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
Virtuoso
posted Hide Post
the key point being that you still MUST have a master file, but this is an excellent and easy way to pull in data where layouts and formats may vary. You just have to write defines for every field. And if you have to do it over and over, you may as well create an MFD for the file you are trying to read. MFD is simpler than all those defines and functions, but user has more control with defines.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
<Umar Farook S>
posted
I have written MFD's but facing some problem here
I will explain at my level best.I have 2 csv files.so i created 2 master files.I am able to read them in webfocus and display them.I have 5 fields in my csv file out of which all 5 fields are key fields which are to be checked for mis-matches between 2 csv files.I have used MATCH FILE concept here to create oen single HOLD file using OLD-NOR-NEW since i need only the mismatches as output in my final output.
I am posting my 2 master files and fex code here for your reference.
Please let me know what changes i need to make and where i have gone wrong.
::Master file1::
FILE=qmtrade1, SUFFIX=COM
SEGNAME=QMTRADE1, SEGTYPE=S1, $
FIELD=MMRefNumber, ALIAS=M01, USAGE=A40, ACTUAL=A40, $
FIELD=TradeDate, ALIAS=M02, USAGE=MDY, ACTUAL=DATE, $
FIELD=Portfolio, ALIAS=M03, USAGE=A40, ACTUAL=A40, $
FIELD=NotionalAmt, ALIAS=M04, USAGE=P16.2C, ACTUAL=P16, $
FIELD=TradeCounterParty, ALIAS=M05, USAGE=A40, ACTUAL=A40, $
FIELD=AssetSubType, ALIAS=M06, USAGE=A40, ACTUAL=A40, $
FIELD=AssetType, ALIAS=M07, USAGE=A40, ACTUAL=A40, $

::Master file2::
FILENAME=qmtrade3,SUFFIX=COM
SEGNAME=QMTRADE3, SEGTYPE=S1, $
FIELD=Number, ALIAS=M001, USAGE=A40, ACTUAL=A40, $
FIELD=Date, ALIAS=M002, USAGE=MDY, ACTUAL=DATE, $
FIELD=Portf, ALIAS=M003, USAGE=A40, ACTUAL=A40, $
FIELD=Notional, ALIAS=M004, USAGE=P16.2C, ACTUAL=P16, $
FIELD=CounterParty, ALIAS=M005, USAGE=A40, ACTUAL=A40, $
FIELD=ASubType, ALIAS=M006, USAGE=A40, ACTUAL=A40, $
FIELD=AType, ALIAS=M007, USAGE=A40, ACTUAL=A40, $


::FEX code::
-* File qmtrade3csv.fex
FILEDEF QMTRADE1 DISK \\IMNT14\SHARED\TRANSFER\NEW_TRADE1.CSV
FILEDEF QMTRADE3 DISK \\IMNT14\SHARED\TRANSFER\NEW_TRADE2.CSV
SET ASNAMES=ON
SET NULL=ON
TABLE FILE QMTRADE1
PRINT
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
ON TABLE HOLD AS QMTRADEONE
END
-RUN
-*
TABLE FILE QMTRADE3
PRINT
ASubType
AType
BY Number
BY Date
BY Portf
BY Notional
BY CounterParty
ON TABLE HOLD AS QMTRADEThree
END
-RUN
-*
MATCH FILE QMTRADEONE
PRINT
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
RUN
FILE QMTRADEThree
SUM
ASubType
AType
BY Number AS NUM
BY Date AS DAT
BY Portf AS PORT
BY Notional AS NOTI
BY CounterParty AS CPTY
AFTER MATCH HOLD OLD-NOR-NEW
END
-RUN
-*
DEFINE FILE HOLD
DELIM/A1 = '|';
END
-RUN
SET NODATA=' '
-*
TABLE FILE HOLD
HEADING
"COMPARE OF TWO CSV FILES"
"---- FILE1 ---------------------------- <65 -----------------------FILE2 --------"
"PRINT
MMRefNumber AS 'MM,Ref,Number'
TradeDate AS 'Trade,Date'
Portfolio AS 'Port,folio'
NotionalAmt AS 'Notional,Amt'
TradeCounterParty AS 'Trade,Counter,Party'
AssetSubType AS 'Asset,Sub,Type'
AssetType AS 'Asset,Type'
DELIM AS ' ' IN +4
NUM
DAT
PORT
NOTI
CPTY
ASubType
AType
BY MMRefNumber NOPRINT
BY TradeDate NOPRINT
BY Portfolio NOPRINT
BY NotionalAmt NOPRINT
BY TradeCounterParty NOPRINT
BY NUM NOPRINT
BY DAT NOPRINT
BY PORT NOPRINT
BY NOTI NOPRINT
BY CPTY NOPRINT
ON TABLE NOTOTAL
etc etc....
 
Report This Post
Virtuoso
posted Hide Post
Umar

You are confusing us with this question, you ahve posted the same question in several discussions here.
It would be better to post it once and if that problem is solved NOT posting a new question in that same discussion, but start a new one.

Now, you start with two comma delimited files, do a query on them and hold them in two hold files.

That is the first error.

You now in fact created two new flat files.

Change the two master description so the fieldnames are the same for the keyfields, and the keep the names short as possible and not longer than 12 characters.
The two datafields should be different.

I would hold them in a Focus format file with indexes and the by statements are in fact not necessary.

TABLE FILE QMTRADE1
PRINT
ASubType
AType
Number
Date
Portf
Notional
CounterParty
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX NUMBER DATE PORTF NOTIONAL COUNTERPARTY
END
TABLE FILE QMTRADE3
PRINT
BSubType
BType
Number
Date
Portf
Notional
CounterParty
ON TABLE HOLD AS HOLD2 FORMAT FOCUS INDEX NUMBER DATE PORTF NOTIONAL COUNTERPARTY
END


The run statements are not needed.

Now you can start with the match statements.

In the MATCH part you start with a PRINT fieldnames BY indexed fields.
the second part should be the same, so not a SUM.

Ad the end you should say

AFTER MATCH HOLD OLD-AND-NEW

Then you will have a combined dataset with the 5 keyfields and the 4 datafields

key1 key2 key3 key4 key5 data1 data2 data3 data4 
one  xxx  aaa  bbb  123  aab   006   aab   006
for  xxy  aad  bbp  125  aacb  007   aacb  006
two  xpp  aaa  bb3  127              abx   781
five xxp  bsa  axd  120  xxx   781


So the first record is the same
in the second data2 and data4 is different
the 3th record has no data from the first file
and the 4th record has no data in the second file.

You can build a query on that set of data to give you all kind of errors.

I hope this will help.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
Hi Frank

I like to know why i cannot use OLD-NOR-NEW
which will give me the mismatches when i need only mismatches between the two files.
So that i can HOLD it and PRINT the HOLD file for report output.

Please advice .
 
Report This Post
Virtuoso
posted Hide Post
Umar

just start with following my advices and you can then create the reports you need.
the hold files need to be focus format.

TABLE FILE HOLD
PRINT *
WHERE data1 NE data3 OR data2 NE data4;
END

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




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
Thanks Very Much for your help Frank.
It worked fine.
I like to have one more clarification.
1.If the input files have 10 fields and i need only 5 in my report do i need to include them while i create MASTER.
 
Report This Post
Virtuoso
posted Hide Post
Yes and No

You have to describe the whole string, so if the first 50 characters are not needed, you have to define them as a filler of A50 and you omit that field in your query.

If your master describes a SQL datasource you can describe only the 5 fields you need and leave the rest out of it.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
Hi Frank,
Sorry to come back again on the code.
I first tried with 5 to 10 rows of data and it worked fine.
But when i give the whole CSV file as input it creates problem again.
Even if the row matches in both files it showed as mis-matched row.I do not know why a zero value is getting displayed for a field.I hope if u have a look at the code and output you can say whats the problem.But i donot know how to attach files here in forum.
 
Report This Post
Virtuoso
posted Hide Post
you can send me the whole FEX code you have created and the two master files and the CSV files, but it will take some time till I come back on this.
I will be out for a two week holiday till the 1st of October.

send it to frank.terlien@gmail.com




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 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     Reading without MASTER file

Copyright © 1996-2020 Information Builders