Is there an easy way to implement a UNION using hold files ...

[Martin Yergeau]

I am not sure how to perform those options using IA, but I think that you could use either the MORE or MATCH feature.

If you have two identical files with same structure, fields name & format, the MORE feature is similar to a SQL UNION.

MATCH feature is giving you more flexibility regarding file/fields format

Here a quick sample using MORE

TABLE FILE CAR

SUM SALES AS 'MEAS'

BY COUNTRY/A10 AS 'DIM'

WHERE COUNTRY EQ 'ENGLAND';

ON TABLE HOLD AS FILE1

END

-RUN

 

TABLE FILE CAR

SUM SALES AS 'MEAS'

BY CAR/A10 AS 'DIM'

WHERE COUNTRY NE 'ENGLAND';

ON TABLE HOLD AS FILE2

END

-RUN

 

TABLE FILE FILE1

SUM MEAS

BY DIM

MORE

FILE FILE2

END

-RUN

Sample for MATCH

TABLE FILE CAR

SUM SALES

BY COUNTRY

ON TABLE HOLD AS FILE1

END

-RUN

 

TABLE FILE CAR

SUM RETAIL

BY COUNTRY

BY CAR

WHERE COUNTRY EQ 'ENGLAND';

ON TABLE HOLD AS FILE2

END

-RUN

 

MATCH FILE FILE1

SUM SALES

BY COUNTRY

RUN

FILE FILE2

SUM RETAIL

BY COUNTRY

BY CAR

AFTER MATCH HOLD AS MRGDATA OLD-OR-NEW

END

-RUN

 

TABLE FILE MRGDATA

PRINT *

END

-RUN

[Debra Waybright]

@lawrence.loncar

If you are trying to capture individuals from two different data sources, it sounds like you need a full outer join, not a left join. A left join will pull everything from the left table and only what is in the right table that joins to it. You cant do a full outer join in IA in 8207.23.

[Larry Loncar]

Is there an easy way to implement a UNION using hold files For example, I am attempting to create a single report that captures individuals from two separate data sources. When I attempt to do a join between the two hold files, it appears to be running a left inner join instead of a left outer join.

I am using InfoAssist version 8207.23

[Sarah Buccellato]

Larry, welcome to myibi! Are you still experiencing this issue If so, please open a support case.