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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Joining files
 Login/Join
 
Gold member
posted
How can I join 2 files and get the proper output I want. File1 has job number and multiple entries for an invoice number, file2 has a job number and multiple entries for sales rep(for split commission purposes).
file1 has job1, invoice1; job1 invoice2; job2, invoice1; etc.
File2 has job1, slsrep1; job1 slsrep2; no job2; etc.

I want to join these files so I have this result:
job1 invoice1 slsrep1, then
job1 invoice1 slsrep2, then
job1 invoice2 slsrep1, then
job1 invoice2 slsrep2, then
job2 invoice1 and empty slsrep#(no split commission).

everything I have tried does not work or i get the following error message
FOC1072 DUPLICATES IN FILE2


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
 
Posts: 67 | Location: Marceline, Missouri | Registered: August 09, 2006Report This Post
Virtuoso
posted Hide Post
Jenny

show us the complete fex and maybe your masters

did you consider MATCH instead of JOIN?

Frank




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
Virtuoso
posted Hide Post
I sometime join on just job number to all job number (in my case students). Do a print * and hold then manipulae, but as Frank says masters and fex help.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Virtuoso
posted Hide Post
Jenny,
It looks like you are joining 2 flat files. If the files are not too big, I would suggest you load them in to FOCUS files. You shouldn't have any problems then.


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
Platinum Member
posted Hide Post
Jenny, what you're seeing is typical when you're joining flat file to flat file there it's a many-to-many relationship. The technique I've always employed is to take the second file and hold it off in FORMAT FOCUS, and index it by the key field. That way, you have one flat file and one FOCUS file. Being that they are different, FOCUS will not actually try to join them together like it does with two flat files, but instead it will read the first record in FILE1, then find all the associated records in FILE2, then read the second record in FILE1, then find all the associated records in FILE2, etc., etc. If the files are very large, this may not be very efficient and as was already suggested, a MATCH may work better for you. Below is my attempt to give you a sample:

TABLE FILE FILE2
PRINT SLSREP
BY JOB
ON TABLE HOLD FORMAT FOCUS INDEX JOB
END
-RUN
JOIN JOB IN FILE1 TO ALL JOB IN FILE2 AS J1
TABLE FILE FILE1
PRINT INVOICE SLSREP
BY JOB
END
-RUN

Good luck.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Virtuoso
posted Hide Post
I agree with the approach Jessica (and Danny) is taking, loading one file into a FOCUS db with an INDEX would keep things simple.

Disagree though with Jessica saying that a JOIN on an INDEX in a FOCUS file is inefficient. This type of access is very efficient and is normally much better than a MATCH.

Would be interested in the MFDs, FEX and maybe more data, as a JOIN between 2 flat files, which I assume you are using because of the error, can be made to work to give you exactly what you want.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Clarification: I said my suggestion MAY not be very efficient, not that it was inefficient. I was allowing for the possibility that this may not work well for Jenny in her situation. I would always try that first before resorting to using a MATCH and it's always worked well for me.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Gold member
posted Hide Post
I have tried reversing the join, I have tried a match and neither work well. I will try creating the focus format and see what that gives me. It is another option. I am relatively new to WebFocus and am learning trial by fire! Thanks for the feedback, and I will let you all know what the outcome is. Thanks again.


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
 
Posts: 67 | Location: Marceline, Missouri | Registered: August 09, 2006Report This Post
Gold member
posted Hide Post

Jenny:

If both data files are sequential flat files, and you have a FOCUS MFD (MASTER FILE) to read both, say one is called JOB, the other SLS, then take it in phases. (External files have a SUFFIX=FIX in the MFD.)

Phase I

TABLEF FILE JOB
PRINT FIELDS YOU WANT
BY JOB
BY INVOICE
ON TABLE HOLD AS HOLDJOB1
IF READLIMIT EQ 100
END
-RUN

TABLE FILE HOLDJOB1
PRINT *
END
-EXIT

Evaluate: Is this what I want? Do I need more or less fields to get the final result? Try it with READLIMIT of 100 or 1000 ; If not, repeat, if OK, continue. Note that TABLEF will retrieve at a high rate and will not sort the BY fields (but put them in order specified).

TABLEF FILE SLS
PRINT FIELDS YOU WANT
BY JOB
BY SLSREP
IF READLIMIT EQ …
ON TABLE HOLD AS HOLDSLS1
END
-RUN

TABLE FILE HOLDSLS1
PRINT *
END
-EXIT

Evaluate this extract to see if it meets your needs etc.

Phase II

Remove the TABLE/PRINT *, or put in "–GOTO label" to bypass and do both TABLEFs with commented-out "IF READLIMIT .." ; get all data. If the data size is very large, you may want to consider using a READLIMIT you can work with until you see a pattern that can be used later to split the data say by Region, or State, or time period etc. You can always add WHERE statements to screen in/out what you want in the TABLEF extracts.

TABLE FILE HOLDJOB1
PRINT FIELDS
BY . .
ON TABLE HOLD AS HOLDJOB2
END
-RUN

TABLE FILE HOLDSLS1
PRINT FIELDS
BY JOB
BY SLSREP
ON TABLE HOLD AS SLS1 FORMAT FOCUS
END
-RUN

JOIN JOB IN HOLDJOB2 TO ALL JOB IN SLS1 AS J1
-RUN
CHECK FILE HOLDJOB2 PICTURE
-RUN
? JOIN
-RUN

TABLE FILE HOLDJOB2
PRINT THE REPORT YOU WANT ..

Note: The second HOLD file can be held as a FOCUS file with keys that are indexed. This allows a join and works well (as long as the number of print fields are reasonable quantity). The file JOINING TO needs the indexes, the from HOLD file does not.

Hope this helps… Jim


WebFOCUS 7.6.11, WINDOWS, HTML, PDF, EXCEL
 
Posts: 77 | Location: Baltimore | Registered: May 31, 2006Report This Post
Gold member
posted Hide Post
[QUOTE]Originally posted by Jim_at_LM:
Jim, I did that stepping through idea and it is a GREAT idea. My join was actually working properly, however.......What is happening is this.

When I join file 1 and file 2 the join works great. This joined flat file has information for the job(file1) and commission percentages(file2) for 3 different reps(which I have to parse into separate records for each rep and also compute $$ using the job informationfrom (file 1). For sake of example on job 12345 total commission is $5000 rep1 gets 1/2 of the commission, rep2 gets 1/4 and rep3 gets 1/4. When I take the joined file and try to parse out the info for rep1,(2500), rep2(1250) rep3(1250), first of all the calculations are correct as I step through at the end of each report. The big problem is the appends is not working correctly. I will put the code in below so you can see what I am doing. It appears that it is writing the 1st file for rep1 then overwriting for rep2 and appending rep3. It does not make sense to me but when I "view source" there is a message that says Format conversion error(foc1346) on the computed fields. However that error is not there when I view the file immediately prior to the appends section. HSTINV2 is the joined file and it joins properly. When I view HSTINV3 the computes are figuring correctly. Also the field definitions are exactly the same. any ideas?

TABLE FILE HSTINV2
PRINT
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
CREP1
CPERC1
CFRC1A
CFRC1B
CREP2
CPERC2
CFRC2A
CFRC2B
CREP3
CPERC3
CFRC3A
CFRC3B
CRMRKS
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
COMPUTE CHRG0/A1 = IF CREP1 EQ 0 THEN 'Y' ELSE '';
COMPUTE CHRG1/A1 = IF CREP1 NE 0 THEN 'N' ELSE '';
COMPUTE CHRG2/A1 = IF CREP3 NE 0 THEN 'N' ELSE IF CREP3 EQ 0 AND CREP2 NE 0 THEN 'Y' ELSE ' ';
COMPUTE CHRG3/A1 = IF CREP3 NE 0 THEN 'Y' ELSE ' ';
COMPUTE SVCOMM/D13.2 = IF SCGTCA NE 0 THEN SCGTCA ELSE IF PROGRM EQ 'W' AND DATINV LE '20060602' THEN SCTTCA ELSE SCTTCA + ADDSRV;
COMPUTE COMMSN0/D13.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN SVCOMM ELSE 0;
COMPUTE MISC0/D13.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN SCMISC ELSE 0;
COMPUTE PRICE0/D13.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN TOTAMT - INNSAL ELSE 0;
COMPUTE COMMSN1/D13.2 = IF CREP1 EQ 0 THEN 0 ELSE IF CPERC1 NE 0 THEN ((SVCOMM * CPERC1) /100) ELSE IF CFRC1A NE 0 THEN (SVCOMM / (CFRC1B * CFRC1A)) ELSE 0;
COMPUTE MISC1/D13.2 = IF CREP1 EQ 0 THEN 0 ELSE IF CPERC1 NE 0 THEN ((SCMISC * CPERC1) / 100) ELSE IF CFRC1A NE 0 THEN (SCMISC / (CFRC1B * CFRC1A)) ELSE 0;
COMPUTE PRICE1/D13.2 = IF CREP1 EQ 0 THEN 0 ELSE IF CPERC1 NE 0 THEN (((TOTAMT - INNSAL) * CPERC1) / 100) ELSE IF CFRC1A NE 0 THEN ((TOTAMT - INNSAL) / (CFRC1B * CFRC1A)) ELSE 0;
COMPUTE COMMSN2/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF CPERC2 NE 0 THEN ((SVCOMM * CPERC2) /100) ELSE IF CFRC2A NE 0 THEN (SVCOMM / (CFRC2B * CFRC2A)) ELSE 0;
COMPUTE COMMSN2T/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF ((COMMSN1 + COMMSN2) NE SVCOMM) THEN (SVCOMM - COMMSN1) ELSE COMMSN2;
COMPUTE MISC2/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF CPERC2 NE 0 THEN ((SCMISC * CPERC2) / 100) ELSE IF CFRC2A NE 0 THEN (SCMISC / (CFRC2B * CFRC2A)) ELSE 0;
COMPUTE MISC2T/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF ((MISC1 + MISC2) NE SCMISC) THEN (SCMISC - MISC1) ELSE MISC2;
COMPUTE PRICE2/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF CPERC2 NE 0 THEN (((TOTAMT - INNSAL) * CPERC2) / 100) ELSE IF CFRC2A NE 0 THEN ((TOTAMT - INNSAL) / (CFRC2B * CFRC2A)) ELSE 0;
COMPUTE PRICE2T/D13.2 = IF CREP2 EQ 0 THEN 0 ELSE IF ((PRICE1 + PRICE2) NE (TOTAMT - INNSAL)) THEN ((TOTAMT - INNSAL) - PRICE1) ELSE PRICE2;
COMPUTE COMMSN3/D13.2 = IF CREP3 NE 0 THEN 0 ELSE IF CPERC3 EQ 0 THEN ((SVCOMM * CPERC3) /100) ELSE IF CFRC3A NE 0 THEN (SVCOMM / (CFRC3B * CFRC3A)) ELSE 0;
COMPUTE COMMSN3T/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF ((COMMSN1 + COMMSN2 + COMMSN3) NE SVCOMM) THEN (SVCOMM - COMMSN1- COMMSN2) ELSE COMMSN3;
COMPUTE MISC2/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF CPERC3 NE 0 THEN ((SCMISC * CPERC3) / 100) ELSE IF CFRC3A NE 0 THEN (SCMISC / (CFRC3B * CFRC3A)) ELSE 0;
COMPUTE MISC3T/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF ((MISC1 + MISC2) + MISC3) NE SCMISC THEN ((SCMISC - MISC1) - MISC2) ELSE MISC3;
COMPUTE PRICE3/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF CPERC3 NE 0 THEN (((TOTAMT - INNSAL) * CPERC3) / 100) ELSE IF CFRC3A NE 0 THEN ((TOTAMT - INNSAL) / (CFRC3B * CFRC3A)) ELSE 0;
COMPUTE PRICE3T/D13.2 = IF CREP3 EQ 0 THEN 0 ELSE IF (((PRICE1 + PRICE2) + PRICE3) NE (TOTAMT - INNSAL)) THEN (((TOTAMT - INNSAL) - PRICE1) - PRICE2) ELSE PRICE3;
BY JOBNO
BY INVCNO
BY JOBSEQ
BY SCRVNO
WHERE ( TOTCOM NE 0 ) OR ( SCGTCA NE 0 ) OR ( ADDSRV NE 0 ) OR ( SCMISC NE 0 ) OR ( SCTTCA NE 0 );
ON TABLE HOLD AS HISTINV3 FORMAT ALPHA
END
-* SELECT OUT THE CURRENT DATE RANGE RECORDS
TABLE FILE HISTINV3
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
CREP1
CPERC1
CFRC1A
CFRC1B
CREP2
CPERC2
CFRC2A
CFRC2B
CREP3
CPERC3
CFRC3A
CFRC3B
CRMRKS
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
CHRG0
CHRG1
CHRG2
CHRG3
COMMSN0
MISC0
PRICE0
COMMSN1
MISC1
PRICE1
COMMSN2T
MISC2T
PRICE2T
COMMSN3T
MISC3T
PRICE3T
BY JOBNO
ON TABLE HOLD AS HISTINV4A FORMAT ALPHA
END
TABLE FILE HISTINV4A
PRINT *
ON TABLE HOLD AS HISTINV4
END
-*SPLIT INTO SEPARATE RECORDS SO YOU CAN FIGURE THE COMMISSION ON THE JOB
FILEDEF YBKSPLITCOM1 DISK YBKSPLITCOM1.FTM (APPEND
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '';
COMPUTE CHARGD/A1 = CHRG0;
COMPUTE CREP/D3 = CURSLS;
COMPUTE CPERC/D2 = 0;
COMPUTE CFRACA/D1 = 0;
COMPUTE CFRACB/D1 = 0;
COMPUTE COMMT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN COMMSN0 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN MISC0 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN PRICE0 ELSE 0;
BY JOBNO
WHERE CREP1 EQ 0 AND CURSLS NE 0;
ON TABLE HOLD AS YBKSPLITCOM1
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG1;
COMPUTE CREP/D3 = CREP1;
COMPUTE CPERC/D2 = CPERC1;
COMPUTE CFRACA/D1 = CFRC1A;
COMPUTE CFRACB/D1 = CFRC1B;
COMPUTE COMMT/D14.2 = IF CREP1 NE 0 THEN COMMSN1 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 NE 0 THEN MISC1 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 NE 0 THEN PRICE1 ELSE 0;
BY JOBNO
WHERE CREP1 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG2;
COMPUTE CREP/D3 = CREP2;
COMPUTE CPERC/D2 = CPERC2;
COMPUTE CFRACA/D1 = CFRC2A;
COMPUTE CFRACB/D1 = CFRC2B;
COMPUTE COMMT/D12.2 = IF CREP2 NE 0 THEN COMMSN2T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP2 NE 0 THEN MISC2T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP2 NE 0 THEN PRICE2T ELSE 0;
BY JOBNO
WHERE CREP2 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG3;
COMPUTE CREP/D3 = CREP3;
COMPUTE CPERC/D2 = CPERC3;
COMPUTE CFRACA/D1 = CFRC3A;
COMPUTE CFRACB/D1 = CFRC3B;
COMPUTE COMMT/D12.2 = IF CREP3 NE 0 THEN COMMSN3T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP3 NE 0 THEN MISC3T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP3 NE 0 THEN PRICE3T ELSE 0;
BY JOBNO
WHERE CREP3 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM1
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
JOBTYP
CUSTNO
SPLTCOM
CHARGD
SCGTCA
COMPUTE CURRENTREP/D3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMPUTE CURSLSREP/P3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMMT
MISCT
PRICET
COMPUTE COMMSN/D13.2 = IF COMMT NE 0 THEN COMMT ELSE 0;
COMPUTE MISC/D13.2 = IF MISCT NE 0 THEN MISCT ELSE 0;
COMPUTE PRICE/D13.2 = IF PRICET NE 0 THEN PRICET ELSE 0;
BY JOBNO
ON TABLE HOLD AS YBKSPLITCOM2 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM2
PRINT *
END
-EXIT


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
 
Posts: 67 | Location: Marceline, Missouri | Registered: August 09, 2006Report This Post
Virtuoso
posted Hide Post
Jenny

A quick look through, and I wonder why you have:
ON TABLE HOLD AS YBKSPLITCOM1
in the first request after issuing the FILEDEF and:
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
for the subsequent requests. These are different file layouts.

Also, the first
COMPUTE SPLTCOM/A1 = '';
appears to not have a space between the 2 quotes.

Try changing those 2 and see what happens.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
[QUOTE]Originally posted by Alan B:
OH MY what an idiot! Yes, those are different. An extra set of eyes is nice to have. It appears that I get past the first format error but now I get this still. < !--
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 11 LINES= 11
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
(FOC1346) : FORMAT ERROR: Record 12 , Column 218
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : PRICET
(FOC1346) : FORMAT ERROR: Record 13 , Column 218
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : PRICET
(FOC1346) : FORMAT ERROR: Record 14 , Column 218
(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : PRICET
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14
0 NUMBER OF RECORDS IN TABLE= 14 LINES= 14

WebFOCUS Version 7.1.4 compiled and linked on Fri May 19 16:45:40 EDT 2006 (Gen branch714:284)
-->

HERE IS THE NEW AND IMPROVED VERSION.


-*SPLIT INTO SEPARATE RECORDS SO YOU CAN FIGURE THE COMMISSION ON THE JOB
FILEDEF YBKSPLITCOM1 DISK YBKSPLITCOM1.FTM (APPEND
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = ' ';
COMPUTE CHARGD/A1 = CHRG0;
COMPUTE CREP/D3 = CURSLS;
COMPUTE CPERC/D2 = 0;
COMPUTE CFRACA/D1 = 0;
COMPUTE CFRACB/D1 = 0;
COMPUTE COMMT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN COMMSN0 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN MISC0 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 EQ 0 AND CURSLS NE 0 THEN PRICE0 ELSE 0;
BY JOBNO
WHERE CREP1 EQ 0 AND CURSLS NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG1;
COMPUTE CREP/D3 = CREP1;
COMPUTE CPERC/D2 = CPERC1;
COMPUTE CFRACA/D1 = CFRC1A;
COMPUTE CFRACB/D1 = CFRC1B;
COMPUTE COMMT/D14.2 = IF CREP1 NE 0 THEN COMMSN1 ELSE 0;
COMPUTE MISCT/D14.2 = IF CREP1 NE 0 THEN MISC1 ELSE 0;
COMPUTE PRICET/D14.2 = IF CREP1 NE 0 THEN PRICE1 ELSE 0;
BY JOBNO
WHERE CREP1 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG2;
COMPUTE CREP/D3 = CREP2;
COMPUTE CPERC/D2 = CPERC2;
COMPUTE CFRACA/D1 = CFRC2A;
COMPUTE CFRACB/D1 = CFRC2B;
COMPUTE COMMT/D12.2 = IF CREP2 NE 0 THEN COMMSN2T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP2 NE 0 THEN MISC2T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP2 NE 0 THEN PRICE2T ELSE 0;
BY JOBNO
WHERE CREP2 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE HISTINV4
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
CUSTNO
TOTAMT
INNSAL
JOBTYP
CURSLS
PROGRM
TOTCOM
SCGTCA
ADDSRV
SCMISC
SCTTCA
SVCOMM
COMPUTE SPLTCOM/A1 = '*';
COMPUTE CHARGD/A1 = CHRG3;
COMPUTE CREP/D3 = CREP3;
COMPUTE CPERC/D2 = CPERC3;
COMPUTE CFRACA/D1 = CFRC3A;
COMPUTE CFRACB/D1 = CFRC3B;
COMPUTE COMMT/D12.2 = IF CREP3 NE 0 THEN COMMSN3T ELSE 0;
COMPUTE MISCT/D12.2 = IF CREP3 NE 0 THEN MISC3T ELSE 0;
COMPUTE PRICET/D12.2 = IF CREP3 NE 0 THEN PRICE3T ELSE 0;
BY JOBNO
WHERE CREP3 NE 0;
ON TABLE HOLD AS YBKSPLITCOM1 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM1
PRINT
INVCNO
JOBSEQ
SCRVNO
DATINV
BILTYP
JOBTYP
CUSTNO
SPLTCOM
CHARGD
SCGTCA
COMPUTE CURRENTREP/D3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMPUTE CURSLSREP/P3 = IF CREP NE 0 THEN CREP ELSE CURSLS;
COMMT
MISCT
PRICET
COMPUTE COMMSN/D13.2 = IF COMMT NE 0 THEN COMMT ELSE 0;
COMPUTE MISC/D13.2 = IF MISCT NE 0 THEN MISCT ELSE 0;
COMPUTE PRICE/D13.2 = IF PRICET NE 0 THEN PRICET ELSE 0;
BY JOBNO
ON TABLE HOLD AS YBKSPLITCOM2 FORMAT ALPHA
END
TABLE FILE YBKSPLITCOM2
PRINT *
END
-EXIT


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
 
Posts: 67 | Location: Marceline, Missouri | Registered: August 09, 2006Report This Post
Gold member
posted Hide Post
JIM,

Nevermind, I am still a dufus, one is defined 12.2 and one is 14.2. I am so glad you guys are out there to help us less experienced goofuses! Let me fix that and see


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
 
Posts: 67 | Location: Marceline, Missouri | Registered: August 09, 2006Report This Post
Gold member
posted Hide Post
ok, it seems like when I make a change, more errors come up. I was not having a problem on the other fields and now they are bombing with format errors.


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
 
Posts: 67 | Location: Marceline, Missouri | Registered: August 09, 2006Report This Post
Silver Member
posted Hide Post
I have a related query

In file 1 i have-
Rollno name day
------- ---- -----
111 aaa mon
111 aaa tue

FIle 2 contains-

Rollno comments
----- --------
111 present
111 absent

Both are flat files
So i hold it in format focus and then join

'join Rollno in file1 to all Rollno in file2'

but it is giving-
Rollno name day comments
------- ---- ---- --------
111 aaa mon present
111 aaa mon absent
111 aaa tue present
111 aaa tue absent

And if i dont use 'all' the it gives-
Rollno name day comments
------- ---- ---- --------
111 aaa mon present
111 aaa tue present

but i want it as-
Rollno name day comments
------- ---- ---- --------
111 aaa mon present
111 aaa tue absent

How do i make sure that the 1st record in file 1 joins to the corresponding 1st record in file 2,
and the 2nd record of file1 joins with the corresponding 2nd record with file 2 ?
Any suggestions?


FOCUS 7.1.1/ MF(OS/390)
 
Posts: 40 | Location: Chennai, India | Registered: January 31, 2007Report This Post
Member
posted Hide Post
Manash,
Before you hold in format focus and join the two files, you can create a define field for each file that is a unique row counter (ROWNUM/I9 = ROWNUM + 1Wink.
Then join on this new field.
That way you get a one to one join for each row.
Hope this is what you wanted to do....


WebFOCUS 760 HPUX - Using MRE, Report Caster, Dashboard and Self Service.
 
Posts: 16 | Location: California | Registered: April 18, 2007Report This Post
Virtuoso
posted Hide Post
Manash

It is a bit confusing to come up with an other question.
try posting this as a new problem, and I (or someone else) will answer there.




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
Platinum Member
posted Hide Post
I am not sure if this helps anyone, but I had the same issue with the FOC1346 error... and the issue was the FORMAT ALPHA... I got rid of it and I didn't get those format errors anymore.

Hope that helps a bit.


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders