Ive done some searching but can't seem to fine exactly what I'm looking for.

What I'm trying to do is create a hierarchy table of our store locations and our calendar. There are no matching keys in the files to create a join. I've tried creating a dummy field, and that works if I only had 1 store.

So what I need is a file that list all my stores (70 currently) with a calendar file - so my file in the end would list each store 365 times to represent each day of the year.

Does any one know if this is possible?

Any help would be a appreciated.

Thanks.

This message has been edited. Last edited by: Kerry,

How did you build your join when you tested with the dummy field, are you working with FOCUS files or database?

Perhaphs:

JOIN FIELD IN TABLE TO ALL FIELD IN ANOTHER_TABLE

Could help with FOCUS files

-SET &ECHO=ON;

-*-- Create the master for CALENDAR1 -----------------------------
FILEDEF DATAMAST DISK CALENDAR1.MAS
-RUN

-*--
-WRITE DATAMAST FILE=CALENDAR1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=A8, $

-*-- Create the data file for CALENDAR1 --------------------------
FILEDEF CALENDAR1 DISK CALENDAR1.FTM
-RUN

-WRITE CALENDAR1 20081124
-WRITE CALENDAR1 20081125
-WRITE CALENDAR1 20081126
-WRITE CALENDAR1 20081127
-WRITE CALENDAR1 20081128
-WRITE CALENDAR1 20081129
-WRITE CALENDAR1 20081130

-*-- Create the report -----------------------------------------------

DEFINE FILE CALENDAR1
CAL_DUMMY1/A1 = '';
END
-RUN

TABLE FILE CALENDAR1
PRINT DATE1
BY CAL_DUMMY1
ON TABLE HOLD AS HCAL FORMAT FOCUS INDEX CAL_DUMMY1
END
-RUN

JOIN CAR_DUMMY1 WITH COUNTRY IN CAR TO ALL CAL_DUMMY1 IN HCAL AS J1

DEFINE FILE CAR
CAR_DUMMY1/A1 WITH COUNTRY = '';
END
-RUN

TABLE FILE CAR
PRINT
DATE1 NOPRINT
BY COUNTRY
BY DATE1
END
-RUN

Depending on the type of tables you're joining, you MAY not need to create a temporary HOLD FORMAT FOCUS file...

If the join doesn't work for you, you can apply the same dummy field logic and do a MATCH.

Cathy,

You say "create a hierarchy table of our store locations and our calendar". I understand that you have 2 tables. Evidently you cannot join them for lack of a common field. In fact you want to add 365 dates to your store table.
This is very similar to the McGuyver technique (you can look it up in the forum) and it is what Francis is proposing to you.

Here is an example of the Master for a McGuyver file:

  
FILENAME=FSEQ,SUFFIX=FIX
 SEGNAME=CHAR1,SEGTYPE=S0
  FIELDNAME=BLANK,BLANK,A1,A1,$
SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
  FIELDNAME=CHAR,CHARS,A1,A1,$
  FIELDNAME=COUNTER,ORDER,I2,I4,$

and of the data:

  
 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER

Notice:
1. the data file MUST start with a space
2. the COUNTER field with is ALIAS=ORDER is an internal field which holds the occurence number of each character.
3. if you need a whole year just make the string 365 characters long

You could then code something like this:

  
-DEFAULT &STARTDATE='01/01/2008';
FILEDEF FSEQ DISK [i]path for file[/i]FSEQ.DAT

JOIN BLANK WITH COUNTRY IN CAR TO BLANK IN FSEQ AS X

DEFINE FILE CAR
BLANK/A1 WITH COUNTRY=' ';
STARTDATE/DMYY='&STARTDATE';
MDATE/DMYY=STARTDATE + COUNTER - 1;
END
TABLE FILE CAR
PRINT MDATE
BY COUNTRY
END

make a calendar file , 2 segments, the first is dummy.
make a storelist file, 2 segments, the first segment is dummy
SET CARTESIAN=ON
JOIN dummy IN CALENDAR TO dummy IN STORE AS J1
TABLE FILE thing
PRINT MONTH BY STORE
ON TABLE HOLD
END
SET CARTESIAN=OFF
..
where calendar is (if you don't want to create it from thin air with FSEQ , MacGyver)
DEFINE FILE something
dummy/A1 WITH somefield = '';
END
TABLE FILE something
SUM dummy NOPRINT BY dummy
SUM MONTH BY dummy
ON TABLE HOLD AS CALENDAR FORMAT FOCUS INDEX dummy
END
and your store file is.
..
TABLE FILE source
SUM dummy NOPRINT BY dummy
SUM STORENAME STORENUMBER etc BY dummy
ON TABLE HOLD AS STORE FORMAT FOCUS INDEX dummy
END
..

Sorry for the late response - but this did work like a charm. Thanks so much for your help!