Hi to all,

I had difficulty finding a subject for this post as I do not know to briefly formulate my request.
I have a request for a complex report. Let me explain through an example on CAR table.

Let me first start with a simple example:

TABLE FILE CAR
SUM CNT.SALES
BY CAR
ACROSS COUNTRY
END

gives the following report:

COUNTRY 
            ENGLAND FRANCE ITALY JAPAN W GERMANY 
CAR 
ALFA ROMEO        0      0     3     0         0 
AUDI              0      0     0     0         1 
BMW               0      0     0     0         6 
DATSUN            0      0     0     1         0 
JAGUAR            2      0     0     0         0 
JENSEN            1      0     0     0         0 
MASERATI          0      0     1     0         0 
PEUGEOT           0      1     0     0         0 
TOYOTA            0      0     0     1         0 
TRIUMPH           1      0     0     0         0 

Now I have a separate table COUNTRIES containing the following data:

COUNTRY     POPULATION
ENGLAND       1111111
FRANCE        2222222
ITALY         3333333
JAPAN         4444444
NETHERLANDS   5555555
W GERMANY     6666666

They want a report that looks like this:

COUNTRY 
            ENGLAND         FRANCE          ITALY           JAPAN           NETHERLANDS     W GERMANY 
CAR         Cars Population Cars Population Cars Population Cars Population Cars Population Cars Population 
ALFA ROMEO     0    1111111    0    2222222    3    3333333    0    4444444    0    5555555    0    6666666
AUDI           0    1111111    0    2222222    0    3333333    0    4444444    0    5555555    1    6666666
BMW            0    1111111    0    2222222    0    3333333    0    4444444    0    5555555    6    6666666
DATSUN         0    1111111    0    2222222    0    3333333    1    4444444    0    5555555    0    6666666
JAGUAR         2    1111111    0    2222222    0    3333333    0    4444444    0    5555555    0    6666666
JENSEN         1    1111111    0    2222222    0    3333333    0    4444444    0    5555555    0    6666666
MASERATI       0    1111111    0    2222222    1    3333333    0    4444444    0    5555555    0    6666666
PEUGEOT        0    1111111    1    2222222    0    3333333    0    4444444    0    5555555    0    6666666
TOYOTA         0    1111111    0    2222222    0    3333333    1    4444444    0    5555555    0    6666666
TRIUMPH        1    1111111    0    2222222    0    3333333    0    4444444    0    5555555    0    6666666

So the challenge is to get the population per country in EVERY ROW/CELL, and also add those countries (like NETHERLANDS) where there are no Car Sales at all.
This is not an outer join from COUNTRIES to CARS, because then the Population will only show on cells where Car Sales > 0 and NETHERLANDS will be on a row with CAR = MISSING.
Neither is it a full cartesion product as there is a matching attribute COUNTRY.

Is it possible to make this with some kind of conditional join?
Or should it be done some MATCH statements?
Can anyone assist to find the right direction.

Martin.

This message has been edited. Last edited by: Martin vK,

I'm pretty sure a MATCH on COUNTRY with OLD-OR-NEW will merge the data.

You could easily create that population file and master and test with a MATCH.

hello,

here is my solution :

I create a master for the COUNTRY list

FILENAME=LSTCOUNTRY, SUFFIX=FIX     , DATASET=c:\ibi\apps\baseapp\lstcountry.ftm, $
  SEGMENT=LSTCOUNT, SEGTYPE=S0, $
    FIELDNAME=PAYS, ALIAS=E01, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=POPULATION, ALIAS=E02, USAGE=I5, ACTUAL=A05, $

 

I create a flat file for data lstcountry.ftm

  
ENGLAND   11111
FRANCE    22222
ITALY     33333
JAPAN     44444
W GERMANY 55555
NETHERLAND66666

I add NETHERLAND as it doesn't exist in CAR database.

Here is the FEX :

  
-*I extract the PAYS and population information
TABLE FILE LSTCOUNTRY
SUM POPULATION
BY PAYS
ON TABLE HOLD AS T1 FORMAT ALPHA
END
-RUN

-*I create a loop using Dialog manager in order to list all available cars from CAR database and I store the result into GLOBAL
-*Out file
APP FI GLOBAL DISK global.ftm (APPEND
-RUN
-SET &CPT = 0 ;
-SET &NB_LOOP = &LINES ;

-LOOP

-READ T1 &COUNTRY.10. &POPULATION.5.

-*Get my car informations
DEFINE FILE CAR
PAYS/A10 = '&COUNTRY' ;
CLE/A26 = PAYS || CAR ;
END
TABLE FILE CAR
PRINT 
COMPUTE POPULATION/I5 = &POPULATION ;
BY CLE
BY PAYS 
BY CAR
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS GLOBAL FORMAT ALPHA
END
-*
-SET &CPT = &CPT + 1 ;
-IF &CPT GT &NB_LOOP GOTO SORTIE ELSE LOOP ;

-SORTIE
-*Now I have a file with a unique key and all cars for each country

-*I generate my SALES count based on CAR master
DEFINE FILE CAR
CLE/A26 = COUNTRY || CAR ;
END

TABLE FILE CAR
SUM CNT.SALES AS 'VENTES' 
BY CLE
BY COUNTRY
BY CAR
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES FOCUS
ON TABLE HOLD AS STEP1 FORMAT FOCUS INDEX CLE
END
-RUN


-*I join all my possibilities to existing cases

JOIN CLE IN GLOBAL TO CLE IN STEP1

TABLE FILE GLOBAL
SUM MAX.VENTES AS 'VENTES' MAX.POPULATION AS 'Population'
BY CAR
ACROSS PAYS
END

That's it

I can send you the files if you wish

Nice Pat

Like Francis, my mind immediately went to MATCH until I realised the cartesian nature of the data required.

So I fell back on McGyver technique (ala Noreen & Art) -

Try this code -

APP FI CTRYPOP DISK CTRYPOP.MAS (LRECL 80
-RUN
-WRITE CTRYPOP
-WRITE CTRYPOP FILE=CTRYPOP,SUFFIX=FOC
-WRITE CTRYPOP SEGNAME=SEG1
-WRITE CTRYPOP FIELD=COUNTRY,     ,A10  ,A10  ,$
-WRITE CTRYPOP FIELD=POPULATION,  ,I9   ,I9   ,$
-RUN

CREATE FILE CTRYPOP
MODIFY FILE CTRYPOP
FIXFORM COUNTRY/A10 POPULATION/A9
DATA
ENGLAND     1111111
FRANCE      2222222
ITALY       3333333
JAPAN       4444444
NETHERLAND  5555555
W GERMANY   6666666
END
-RUN

DEFINE FILE CTRYPOP
  MCGYVER/A1 = 'x';
END
DEFINE FILE CAR
  MCGYVER/A1 WITH COUNTRY = 'x';
  CTRYCAR/A36 = COUNTRY | CAR;
END

TABLE FILE CTRYPOP
PRINT POPULATION
   BY MCGYVER
   BY COUNTRY
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE CAR
PRINT CAR
   BY MCGYVER
ON TABLE HOLD AS TEMPHLD2 FORMAT FOCUS INDEX MCGYVER
END
-RUN

TABLE FILE CAR
  SUM CNT.SALES
   BY CTRYCAR
ON TABLE HOLD AS TEMPHLD3 FORMAT FOCUS INDEX CTRYCAR
END
-RUN

JOIN CLEAR *
JOIN MCGYVER          IN TEMPHLD1 TAG T1 TO MULTIPLE MCGYVER IN TEMPHLD2 TAG T2 AS J0
JOIN CTRYCAR WITH CAR IN TEMPHLD1        TO          CTRYCAR IN TEMPHLD3 TAG T3 AS J1

DEFINE FILE TEMPHLD1
  CTRYCAR/A36 WITH CAR = COUNTRY | CAR;
END

TABLE FILE TEMPHLD1
  SUM SALES          AS 'Count,of Sales'
      MAX.POPULATION AS 'Population'
   BY CAR            AS ''
ACROSS COUNTRY       AS ''
ON TABLE SET PAGE NOLEAD
END
-EXIT

Thanks Pat and Tony for your excellent solutions.
I was also thinking along the way of Tony.

So first step to make a cartesian product of CAR.CAR and CTRYPOP.COUNTRY, although I have never used this McGyver technique for this, I would use a conditional join, which will work alike. I assume it depends on your sources (focus/flat file/rdbms) which will suit best.
Next step to make an outer join to the CAR table.

Nice, thanks again.

Great work.

Now, where's that McGyver button in the GUI?

quote:

McGyver button in the GUI?

Never mind that, I would have thought that you would be looking for the documentation

T