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     [CLOSED] Join and conditional define
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Join and conditional define
 Login/Join
 
Member
posted
Hello,

I am trying to build a request based on 3 files.
First file has productid, distributor and manufacturer
Second file has distributor, distributor_code and distributor_status
Third file has manufacturer, manufacturer_code and manufacturer_status

In the output I would like to list the PRODUCTID from PRODUCT, the distributor_code from DISTCO and manufacturer_code from MANUFCO in a single field COMPANY_CODE and company_status from DISTCO and manufacturer_status from MANUFCO in a single field STATUS

Also in the case where distributor code and manufacturer code are equal STATUS would be 'DIST/MANUF' and output will be only one row.

Here is an example:
INPUT
PRODUCT
PRODUCTID MANUFACTURER DISTRIBUTOR
PRODUCT1 COMPANY1 COMPANY2
PRODUCT2 COMPANY3 COMPANY3

MANUFCO
MANUFACTURER MANUFACTURER_CODE MANUFACTURER_STATUS
COMPANY1 10001 MANUFACTURER
COMPANY3 10003 MANUFACTURER

DISTCO
DISTRIBUTOR DISTRIBUTOR_CODE DISTRIBUTOR_STATUS
COMPANY2 10000 DISTRIBUTOR
COMPANY3 10003 DISTRIBUTOR

OUTPUT
PRODUCTID COMPANY_CODE STATUS
PRODUCT1 10000 DISTRIBUTOR
PRODUCT1 10001 MANUFACTURER
PRODUCT2 10003 DIST/MANUF

Does anyone know how to do this?

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8.1.0.5
Windows 10
xls, csv, dbf
 
Posts: 5 | Location: France | Registered: November 24, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
I think this is the solution direction:

JOIN MANUFACTURER IN MANUFCO TO MANUFACTURER IN PRODUCT AS J1

JOIN DISTRIBUTOR IN DISTCO TO DISTRIBUTOR IN PRODUCT AS J2

DEFINE FILE MANUFCO
COMPANY/A20= MANUFACTURER_CODE;
STATUS/A10=IF PRODUCT.MANUFACTURER EQ PRODUCT.DISTRIBUTOR THEN 'DIST/MANUF' ELSE 'MANUFACTURER';
END

DEFINE FILE DISTCO
COMPANY/A20= DISTRIBUTOR_CODE;
STATUS/A10='DISTRIBUTOR';
END

TABLE FILE MANUFACTURER
BY PRODUCTID
BY COMPANY
BY STATUS
MORE
FILE DISTRIBUTOR
WHERE PRODUCT.MANUFACTURER NE PRODUCT.DISTRIBUTOR
END


Test: WF 8.2
Prod: WF 8.2
DB: Progress, IBM DB2, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Hadoop.
 
Posts: 317 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Join and conditional define

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