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,

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