How to combined two tables without using joins

[#1]

Hi All,

I am currently trying to consolidate data from two different tables into on table each table has roughly around 1 Million Records, and a Key that is a date.

When I load data from their respective resident tables via a Left Join, my memory usage raises and the loading takes forever.

Here is the code that loads from the resident tables

[Final MATDOC]:
NOCONCATENATE
LOAD
     [Entered on_MATDOC],     
    [Mat Doc_MATDOC], 
     [Posting_MATDOC]
RESIDENT [TMP_ECC_MATDOCS];
LEFT JOIN
LOAD
     [Entered on_MATDOC],
     [Material Doc_MATDOCBW],
    [Posting Date_MATDOCBW]
RESIDENT [TMP_BW_MATDOCS];

[Final MATDOC]:
NOCONCATENATE
LOAD
    [Entered on_MATDOC],    
    [Mat Doc_MATDOC],
    [Posting_MATDOC]
RESIDENT [TMP_ECC_MATDOCS];
LEFT JOIN
LOAD
    [Entered on_MATDOC],
    [Material Doc_MATDOCBW],
    [Posting Date_MATDOCBW]
RESIDENT [TMP_BW_MATDOCS];

What am I doing wrong in the above? I basically need to compare as follows:

PSeudo: If  [Mat Doc_MATDOC] = [Material Doc_MATDOCBW] then return 1 else 0 //I will count the number of ones to determine the percentage found based on total records in source

Any assistance will be highly appreciated

[#2]

I may have found a solution.

All i need to do is basically check if a value in on table exists in another...so I did the following:

NOCONCATENATE
[Final MATDOC]:
LOAD
 [CompKey_MATDOC],
    [Entered on_MATDOC],     
    [Mat Doc_MATDOC], 
    [Posting_MATDOC]
RESIDENT [TMP_ECC_MATDOCS];
LEFT JOIN
LOAD
    [CompKey_MATDOC],
    'Y' as [Found_in_ECC]
RESIDENT [TMP_BW_MATDOCS];

I create a composite key to join on, the left join will add the field [Found in ECC] with value 'Y' if a join was possible on the compkey where there was a match.

Resulting fields in [Final MATDOC] table:

[CompKey_MATDOC] | [Entered on_MATDOC] | [Mat Doc_MATDOC] | [Posting_MATDOC] | [Found_in_ECC]

Instead of combining all the fields from one table into the other, the left join will add on the field [Found_in_ECC] if it can find an exact match in terms of keys on both tables, which is essentially what i need to do.

[Jason_Michaelides]

Cool, although I thought you said the LEFT JOIN was slowing things down? ApplyMap() is generally faster.

[#4]

I did say that but for some reason if I didn't use the above approach (even though I was trying to avoid using joins) i was getting a cartesian product. I wasn't able to get the ApplyMap to work, I was getting an error (think I posted the error in a previous post).

I will keep trying to use the ApplyMap method to make the code more efficient and post the new solution, in keeping with the Topic.

Thank you all for your contribution and assistance.

[#5 · Accepted solution]

Hi All,

Just an update. I landed up creating a composite Key and doing a full join on the data which seemed to work. Reason for a full join was because I still needed the data that did not have a composite key that matched to illustrated what records need attention.

here is some code:

NOCONCATENATE
[TMP_ECC_MATDOCS]:
LOAD
    [Entered On]&[Material Doc] as [CompKey_MATDOC],
    [Entered On] as [Entered on_MATDOC],     
    [Material Doc] as [Mat Doc_MATDOC], 
    [Posting Date] as [Posting_MATDOC],
    [Amount in LC] as [Amount in LC_MATDOC], 
    Quantity as Quantity_MATDOC
RESIDENT [ECC_TABLE];
//WHERE [Entered on] = $(reconFileDate) - 1;
NOCONCATENATE
[TMP_BW_MATDOCS]:
 LOAD  
     [Entered on]&[Material Doc_BW] as [CompKey_MATDOC],
     [Entered on] as [Entered on_MATDOCBW],
    [Material Doc_BW] as [Material Doc_MATDOCBW],
    [Posting Date_BW] as [Posting Date_MATDOCBW],
    [Amount in LC_BW] as [Amount in LC_MATDOCBW],
    Quantity_BW as Quantity_MATDOCBW
RESIDENT [BW_TABLE];
//WHERE [Entered on] = $(reconFileDate) - 1;;
NOCONCATENATE
[Final MATDOC]: //
LOAD
    [CompKey_MATDOC],
    [Entered on_MATDOC],     
    [Mat Doc_MATDOC], 
    [Posting_MATDOC],
    [Amount in LC_MATDOC], 
    Quantity_MATDOC
RESIDENT [TMP_ECC_MATDOCS];
JOIN ([Final MATDOC])
LOAD
    [CompKey_MATDOC],
    [Amount in LC_MATDOCBW],
    Quantity_MATDOCBW,
    'Y' as [Found_in_ECC]
RESIDENT [TMP_BW_MATDOCS];

Hope it makes sense....