Intermediate Results returns LESS results than Execute
To explain why the results differ when using Intermediate Results v Execute
When using multiple 'joins', the dataset is reduced at different points
Where to find the Intermediate Results option ...
When you are checking an APD, a handy method is to step through each node using the 'Intermediate Results' option. This can be found in the Context Menu of any node. You simply right-click on the node and select 'Intermediate Result. A temporary table is created with the suffix /BIC/000Axxx
Intermediate Results with a JOIN ...
When an Intermediate Result is saved, that saved dataset progresses to the next node of the APD. However, if you use multiple intermediate result tables, because each node is processed as an individual stand-alone component, the results can be distorted.
Due to the processing logic of the analysis process, the processing is pushed to the preceding nodes and hence the selection criteria used or the join condition is not taken into account. But for example, if intermediate result is calculated on the FIRST join, then it will still succeed as the processing for this node has already happened. Therefore you see that there is a discrepancy in the records. Calculations at intermediate result time should not be taken as valid for use in full execution of the analysis process. If necessary delete the temporary tables to ensure true working results in Execute mode.