I have two files of data as shown:

FILE CAR1

COUNTRY CAR
------- -------
ENGLAND JAGUAR
ENGLAND TRIUMPH
FRANCE  CITROEN
FRANCE  PEUGEOT
FRANCE  BUGATTI
CANADA  ZENN

FILE CAR2

COUNTRY PERSON   AMOUNT
------- ------- -------
ENGLAND JAMES   124,010
ENGLAND TONY    427,612
ENGLAND ROBERT   37,822
FRANCE  JULES   102,914
FRANCE  SIMONE  301,973
CANADA  FRANCIS   3,528
CANADA  SEAN    293,872

I would like to join them to get the following result - the AMOUNT is divided among each CAR for the COUNTRY of the PERSON:

COUNTRY CAR     PERSON  AMOUNT
------- ------- ------- ------
ENGLAND JAGUAR  JAMES   062005
ENGLAND JAGUAR  TONY    213806
ENGLAND JAGUAR  ROBERT  018911

ENGLAND TRIUMPH JAMES   062005
ENGLAND TRIUMPH TONY    213806
ENGLAND TRIUMPH ROBERT  018911

FRANCE  CITROEN JULES   034305
FRANCE  CITROEN SIMONE  100658

FRANCE  PEUGEOT JULES   034305
FRANCE  PEUGEOT SIMONE  100658

FRANCE  BUGATTI JULES   034305
FRANCE  BUGATTI SIMONE  100658

CANADA  ZENN    FRANCIS 003528
CANADA  ZENN    SEAN    293872

My incomplete program:

-SET &ECHO=ALL;

SET ASNAMES    = ON
SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
-RUN

FILEDEF MASTER DISK CAR1.MAS
-RUN
-WRITE MASTER FILENAME=CAR1, SUFFIX=FIX
-WRITE MASTER SEGNAME=CAR1
-WRITE MASTER FIELDNAME=COUNTRY, USAGE=A07, ACTUAL=A07, $
-WRITE MASTER FIELDNAME=CAR,     USAGE=A07, ACTUAL=A07, $
-RUN

FILEDEF CAR1 DISK CAR1.TXT
-RUN

-WRITE CAR1 ENGLANDJAGUAR
-WRITE CAR1 ENGLANDTRIUMPH
-WRITE CAR1 FRANCE CITROEN
-WRITE CAR1 FRANCE PEUGEOT
-WRITE CAR1 FRANCE BUGATTI
-WRITE CAR1 CANADA ZENN

FILEDEF MASTER DISK CAR2.MAS
-RUN
-WRITE MASTER FILENAME=CAR2, SUFFIX=FIX
-WRITE MASTER SEGNAME=CAR2
-WRITE MASTER FIELDNAME=COUNTRY, USAGE=A07, ACTUAL=A07, $
-WRITE MASTER FIELDNAME=PERSON,  USAGE=A07, ACTUAL=A07, $
-WRITE MASTER FIELDNAME=AMOUNT,  USAGE=D06, ACTUAL=A06, $
-RUN

FILEDEF CAR2 DISK CAR2.TXT
-RUN

-WRITE CAR2 ENGLANDJAMES  124010
-WRITE CAR2 ENGLANDTONY   427612
-WRITE CAR2 ENGLANDROBERT 037822
-WRITE CAR2 FRANCE JULES  102914
-WRITE CAR2 FRANCE SIMONE 301973
-WRITE CAR2 CANADA FRANCIS003528
-WRITE CAR2 CANADA SEAN   293872

TABLE FILE CAR1
COUNT CAR AS CNT_CAR
BY COUNTRY
PRINT
CAR NOPRINT
BY COUNTRY
BY CAR
ON TABLE HOLD AS HCAR1 FORMAT FOCUS INDEX COUNTRY
END
-RUN

TABLE FILE CAR2
SUM
AMOUNT
BY COUNTRY
BY PERSON
ON TABLE HOLD AS HCAR2 FORMAT FOCUS INDEX COUNTRY
END
-RUN

?FF HCAR1
?FF HCAR2

JOIN COUNTRY IN HCAR1 TO ALL COUNTRY IN HCAR2 AS J01
-RUN

TABLE FILE HCAR1
PRINT
HCAR1.COUNTRY
HCAR1.CNT_CAR
HCAR1.CAR
HCAR2.COUNTRY
HCAR2.PERSON
HCAR2.AMOUNT
END
-RUN

I have applied the MacGyver technique in the past to join two files with no relation to each other to generate new rows, but I can't figure out how to join two files that have a key field in common...

Thanks very much,

Francis,

I'm working on this. I'll let you know when I have something. You obviously have a mnay-to-many and will need to invoke the multiplicative effect. MacGyver may be the way to go but I'm not sure yet.

Join is not going to help you.

Ginny, thank you!

SQL
SELECT CAR.COUNTRY,
CAR,
PERSON,
AMT
FROM CAR JOIN CAR2 ON CAR.COUNTRY=CAR2.COUNTRY
ORDER BY CAR.COUNTRY, CAR, PERSON;
TABLE ON TABLE PCHOLD FORMAT EXL2K
END

Glenda's code works great. You just have to add the counter with a COMPUTE.

Using your file names:

SQL
SELECT HCAR2.COUNTRY,
CAR,
PERSON,
AMOUNT
FROM HCAR2 JOIN HCAR1 ON HCAR1.COUNTRY=HCAR2.COUNTRY
ORDER BY HCAR2.COUNTRY, HCAR2.PERSON;
TABLE ON TABLE HOLD AS HCAR3
END
TABLE FILE HCAR3
PRINT *
END  

Couldn't have done this without Glenda's excellent technique. I'm not an SQL person.

SET ASNAMES    = ON
SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
SET BYDISPLAY  = ON
-RUN

FILEDEF MASTER DISK car1.mas
-RUN
-WRITE MASTER FILENAME=CAR1, SUFFIX=FIX
-WRITE MASTER SEGNAME=CAR1
-WRITE MASTER FIELDNAME=COUNTRY, USAGE=A07, ACTUAL=A07, $
-WRITE MASTER FIELDNAME=CAR,     USAGE=A07, ACTUAL=A07, $
-RUN

FILEDEF CAR1 DISK car1.txt
-RUN

-WRITE CAR1 ENGLANDJAGUAR
-WRITE CAR1 ENGLANDTRIUMPH
-WRITE CAR1 FRANCE CITROEN
-WRITE CAR1 FRANCE PEUGEOT
-WRITE CAR1 FRANCE BUGATTI
-WRITE CAR1 CANADA ZENN


FILEDEF MASTER DISK car2.mas
-RUN
-WRITE MASTER FILENAME=CAR2, SUFFIX=FIX
-WRITE MASTER SEGNAME=CAR2
-WRITE MASTER FIELDNAME=COUNTRY, USAGE=A07, ACTUAL=A07, $
-WRITE MASTER FIELDNAME=PERSON,  USAGE=A07, ACTUAL=A07, $
-WRITE MASTER FIELDNAME=AMOUNT,  USAGE=D06, ACTUAL=A06, $

FILEDEF CAR2 DISK car2.txt
-RUN

-WRITE CAR2 ENGLANDJAMES  124010
-WRITE CAR2 ENGLANDTONY   427612
-WRITE CAR2 ENGLANDROBERT 037822
-WRITE CAR2 FRANCE JULES  102914
-WRITE CAR2 FRANCE SIMONE 301973
-WRITE CAR2 CANADA FRANCIS003528
-WRITE CAR2 CANADA SEAN   293872

TABLE FILE CAR1
-*COUNT CAR AS CNT_CAR
-*BY COUNTRY
PRINT
CAR 
BY COUNTRY
ON TABLE HOLD AS HCAR1 FORMAT ALPHA
END
 
TABLE FILE CAR2
SUM
AMOUNT
BY COUNTRY
BY PERSON
ON TABLE HOLD AS HCAR2 FORMAT FOCUS INDEX COUNTRY
END


SQL
SELECT HCAR1.COUNTRY,
CAR,
PERSON,
AMOUNT
FROM HCAR2 JOIN HCAR1 ON HCAR1.COUNTRY=HCAR2.COUNTRY
ORDER BY HCAR1.COUNTRY, HCAR1.CAR;
TABLE 
ON TABLE HOLD AS HCAR3 FORMAT FOCUS INDEX COUNTRY
END
 
TABLE FILE HCAR3
COUNT DST.CAR AS CARCNT
BY COUNTRY
ON TABLE HOLD AS CARCNT 
END


JOIN COUNTRY IN CARCNT TO ALL COUNTRY IN HCAR3 AS J01

DEFINE FILE CARCNT 
NEWAMT/D8=AMOUNT/CARCNT;
END
TABLE FILE CARCNT 
PRINT 
PERSON NEWAMT AS AMOUNT
BY COUNTRY 
BY CAR SKIP-LINE

END
  

What do you think? You'll have to add the styling and go through it to see if you can eliminate some of the holds.

Thank you, Glenda and Ginny!

What do you call this kind of SQL join?

Here's the code that gives me what I'm looking for:

TABLE FILE CAR1
COUNT CAR AS CNT_CAR
BY COUNTRY
PRINT
CAR NOPRINT
BY COUNTRY
BY CAR
ON TABLE HOLD AS HCAR1
END
-RUN

SQL
SELECT
HCAR1.COUNTRY,
HCAR1.CAR,
HCAR1.CNT_CAR,
CAR2.PERSON,
CAR2.AMOUNT / HCAR1.CNT_CAR AMOUNT
FROM CAR2
JOIN HCAR1 ON HCAR1.COUNTRY = CAR2.COUNTRY
ORDER BY
HCAR1.COUNTRY,
HCAR1.CAR,
CAR2.PERSON;
TABLE
ON TABLE HOLD AS HCAR3
END
-RUN

TABLE FILE HCAR3
PRINT *
END
-RUN

I knew you could do it in fewer notes!

Francis,

It's called an "inner" join. An "inner" join selects only those records from both tables that have matching values. An "outer" join selects all records in one table and any records in the second table that have matching values. There are two types of "outer" joins. In a left outer join, all records from the first table are included. In a right out join, all records from the second table are included.

Now who am I kidding, you probably already know all this. I just posted it for anyone else who might not know. I use these so that I won't have to sort my tables by my join fields or worry about "duplicates" in my join from file.

Good luck

Glenda,

Thanks for the explanation, but isn't this "inner" join different from the normal one because in this case, I want the multiplicative effect to occur?

Cheers,

Francis,

Alas, I have always known that I am not a very good mentor. This fact is supported by my next statement. I don't know the answer to your question, I just know it works. It's a carry over from writing MS Access Database code and QMF code.

And I'm glad it works

Francis,

An inner join returns all rows that are matched even if it causes a multiplicative effect. The difference here is that FOCUS also joins as 1-1 or 1-n by using the ALL keyword in the join.

Even if you use the ALL keywork this is still considered an inner join. In fact, if you do a 1-1 join in FOCUS, this actually acts more as an outer join as it will return all rows from the parent table.

Glenda's code could also be done as

JOIN COUNTRY IN CAR TO ALL COUNTRY IN CAR2
TABLE FILE CAR
PRINT AMOUNT
BY CAR.COUNTRY
BY CAR
BY PERSON
END

However, since you are dealing with flat files, you will probably get out of sequence errors. This can be resolved by creating the child table (car2) as a focus file indexed on COUNTRY.

You also wanted the amount to be divided up evenly for each car per person?

Here is the code that I used to do that.

TABLE FILE CAR1
BY COUNTRY
BY CAR
ON TABLE HOLD AS CAR3 FORMAT FOCUS INDEX COUNTRY
END
-RUN
JOIN CLEAR *
JOIN COUNTRY IN CAR2 TO ALL COUNTRY IN CAR3
TABLE FILE CAR2
SUM CNT.CAR
BY PERSON
PRINT
COMPUTE AVGAMOUNT/P12.2 = AMOUNT / C1;
BY PERSON
BY CAR
BY COUNTRY
END

Gizmo,

I originally tried your approach but made both files FOCUS DB's, therefore the JOIN wasn't giving me the results I was expecting.

I didn't think of your simple solution - so thank you.

Cheers,

somebody buy me a vowel...whats a ZENN?

Zenn electric car company in Toronto
T

Zero Emission No Noise - a "Neighborhood Electric Vehicle"

and STOOOPIDLY not allowed on the streets of Canada!!!

Even though it's built in a small town outside Montreal.

i looked it up, with T's link. its so cute..its perfect for Bonaire. its classified here as a golf-cart, as it goes 25mph max; i want one!

I don't have a licence. I'd love to drive a golf cart!