Hi,

Can i join two hold files with different BY fields.

  
-SET ECHO=ALL;
-*APP HOLD EDAILY_PROD
DEFINE FILE ACTIVITIES_DB
Defyear/YY=OFFICER_DAILY_DATE;
Defmonth/MT=OFFICER_DAILY_DATE;
Defmonthchar/A5V=IF Defmonth EQ 1 THEN 'JAN' ELSE IF Defmonth EQ 2 THEN 'FEB' ELSE IF Defmonth EQ 3 THEN 'MAR' ELSE IF Defmonth EQ 4 THEN 'APR' ELSE IF Defmonth EQ 5 THEN 'MAY' ELSE IF Defmonth EQ 6 THEN 'JUN' ELSE IF Defmonth EQ 7 THEN 'JUL' ELSE IF Defmonth EQ 8 THEN 'AUG' ELSE IF Defmonth EQ 9 THEN 'SEP' ELSE IF Defmonth EQ 10 THEN 'OCT' ELSE IF Defmonth EQ 11 THEN 'NOV' ELSE 'DEC';
FILE_CLASS_DASHBD/A5=IF FILE_CLASS_CODE EQ '54002' THEN '54002' ELSE IF FILE_CLASS_CODE EQ '93001' THEN '93001' ELSE EDIT( FILE_CLASS_CODE, '9999' );
FILE_CLASS_DASHBD_DESC/A75V=IF FILE_CLASS_DASHBD EQ '0900' THEN 'AAA' ELSE IF FILE_CLASS_DASHBD EQ '1100' THEN 'BBB' ELSE IF FILE_CLASS_DASHBD EQ '1200' THEN 
'CCC' ELSE IF FILE_CLASS_DASHBD EQ '1300' THEN 'DDD' ELSE IF FILE_CLASS_DASHBD EQ '2200' THEN 'EEE' ELSE IF FILE_CLASS_DASHBD EQ '2300' THEN 'FFF' ELSE \
IF FILE_CLASS_DASHBD EQ '3500' THEN 'GGG' ELSE IF FILE_CLASS_DASHBD EQ '5000' THEN 'HHH' ELSE IF FILE_CLASS_DASHBD EQ '54002' THEN 'III' ELSE IF 
FILE_CLASS_DASHBD EQ '93001' THEN 'JJJ' ELSE LCWORD3( 75, FILECLASS_DESC, 'A75' );
VIOLENT_FLAG/I9=IF FILE_CLASS_DASHBD IN ( '1300', '1200', '1100', '0900' ) THEN 1 ELSE 0;
END
TABLE FILE ACTIVITIES_DB
SUM
	COMPUTE VALACTIVITYID/I8 = CNT.ACTIVITY_ID;
	COMPUTE VAL/D12.2 = VALACTIVITYID;
BY LOWEST ACTIVITIES_DB.ACTIVITIES_DB.FILE_CLASS_DASHBD_DESC
BY Defyear
BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(Defyear);
BY Defmonth
WHERE Defyear IN ('2016','2017');
WHERE VIOLENT_FLAG EQ 1;
ON TABLE HOLD AS EXTDATA
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN


DEFINE FILE EXTDATA
SALES1 /D6 = IF Defyear EQ 2016 THEN VALACTIVITYID ELSE 0;
SALES2 /D6 = IF Defyear EQ 2017 THEN VALACTIVITYID ELSE 0;
END
TABLE FILE EXTDATA
SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT
	COMPUTE VAL/D12.2 = PCT;
BY LOWEST FILE_CLASS_DASHBD_DESC
BY TOTAL COMPUTE Defyear/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change';
BY Defmonth
ON TABLE HOLD AS PCTDATA
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

DEFINE FILE EXTDATA
SALES1 /D6 = IF Defyear EQ 2016 THEN VALACTIVITYID ELSE 0;
END
TABLE FILE EXTDATA
SUM

	COMPUTE VAL/D12.2 = SALES1;
BY LOWEST FILE_CLASS_DASHBD_DESC 
BY TOTAL COMPUTE Defyear/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A20V = 'Total 2016';
BY Defmonth
ON TABLE HOLD AS PCTDATA1
END
-RUN


TABLE FILE EXTDATA
SUM
     EXTDATA.EXTDATA.VAL AS ''
-*BY LOWEST EXTDATA.EXTDATA.FILE_CLASS_DASHBD_DESC AS 'DETROIT'
BY  EXTDATA.EXTDATA.Defyear NOPRINT
BY  EXTDATA.EXTDATA.ROWLABEL AS ''
ACROSS EXTDATA.EXTDATA.Defmonth AS ''
ACROSS-TOTAL
ON TABLE NOTOTAL
ON TABLE SET STYLE *
     INCLUDE = IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENInformationBuilders_DarkComp.sty,
$
ENDSTYLE
MORE
FILE PCTDATA
MORE
FILE PCTDATA1
END
-RUN

Here i dont want the VAL compute field in PCDATA1 to be calculated for each FILE_CLASS_DASHBD_DESC.
I want it to be calculated for the entire year.
Since i am joning the HOLD files i am not able to achieve that.

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

Have you considered switching to HOLD FORMAT FOCUS INDEX fieldname?

I'm not sure but I don't think you can accomplish this with a fixed format hold file.

Where i should mention in the code that the VAL in PCDATA1 should not have FILE_CLASS_DASHDB-DESC as BY field.?

i have tried using HOLD AS PCDATA1 FORMAT FOCUS.

Index the field you want to use in a JOIN.

srajeevan,

Pay attention and look at your other post.

TABLE FILE 123
...
MORE
FILE abc
END

it's not a JOINning of files. It's a concatenation of files (kind of append). So yes all the involved files need to be exactly the same : number of fields and field's format.

quote:

Can i join two hold files with different BY fields.

In you sample you are doing Universal concatenation( UNION ALL in SQL) not join ,

For join ,The field type must be the same for the common fields, but the field name and field format do not need to be the same.

Okay.Thanks MartinY and BabakNYC.
Will try the suggestions.