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
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
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],
I would do something like the following, but this assumes that you only have one possible [Material Doc_MATDOCBW] per [Entered on_MATDOC]. Then you can make your comparison directly in the Final table.
MappingTable:
Mapping LOAD
[Entered on_MATDOC],
[Material Doc_MATDOCBW]
RESIDENT [TMP_BW_MATDOCS];
[Final MATDOC]:
LOAD
ApplyMap('MappingTable',[Entered on_MATDOC],null()) as [Material Doc_MATDOCBW],
You seem to have a lot of consecutive RESIDENT loads in your script. Can you post your app so we can see the script? And maybe some sample source data...
I won't be able to post sample data as it is company information, however here it some more code and my logic
Logic:
There are two source of data, i need to compare the two sources to see if they match. I.e. What System 1 has, must also me in System 2 (I need to keep the history of the two systems in order to do a successfully comparison.
1. Check if History QVD exists for System 1 -> If not, then load new data, if QVD exists, load the QVD and concate the new data
2. Do the same as step 1 for System 2, but add on relevant [Entered on] field to System 2 table (for delete/reload of same days data)
3. [Troublesome step]: combine certain fields into one table for comparing the two systems
4. Drop the resident tables (System 1 & 2)
Code: (The code below precedes the code i'm having trouble with)
/////////////////////////////////////////////////////////////////////////////////////// // [START] - ECC DATA TABLE - Full load for ECC data // /////////////////////////////////////////////////////////////////////////////////////// IF(NOT isNull(QvdCreateTime('History\ECC_HISTORY.QVD'))) THEN //Does the History QVD for ECC Exist? If yes, then load that data first and append new data to it
[TMP_ECCTABLE]: LOAD [Material Doc], Mat_Doc_Year, [Posting Date], [Entered on], [Entered at], [Amount in LC], Quantity FROM History\ECC_HISTORY.QVD (qvd) WHERE ([Entered on] <> ($(reconFileDate)-1) OR [Entered on] <> ($(reconFileDate)));
Concatenate([TMP_ECCTABLE]) LOAD @1 as [Material Doc], @2 as Mat_Doc_Year, @3 as [Posting Date], @4 as [Entered on], @5 as [Entered at], @6 as [Amount in LC], @7 as Quantity FROM $(zECCFileToArchive) (txt, codepage is 1252, no labels, delimiter is ',', msq);
ELSE //In place when model is reloaded for the first time and there's no history [ECC_TABLE]: LOAD @1 as [Material Doc], @2 as Mat_Doc_Year, @3 as [Posting Date], @4 as [Entered on], @5 as [Entered at], @6 as [Amount in LC], @7 as Quantity FROM $(zECCFileToArchive) (txt, codepage is 1252, no labels, delimiter is ',', msq);
ENDIF
STORE [ECC_TABLE] INTO "History\ECC_HISTORY.QVD" (qvd); //Store all data into the History QVD /////////////////////////////////////////////////////////////////////////////////////// // [END] - ECC DATA TABLE - Full load for ECC data // ///////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////// // [START] - BW DATA TABLE - Full load for BW data // /////////////////////////////////////////////////////////////////////////////////////// IF (NOT isNull(QvdCreateTime('History\BW_HISTORY.QVD'))) THEN //Does the History QVD for BW Exist? If yes, then load that data first and append new data to it
[TMP_BWTABLE]: LOAD @1 as [Material Doc_BW], @2 as [Mat Doc Year_BW], @3 as [Posting Date_BW], @4 as [Total Stock - Receipt], @5 as [Total Stock - Issue], @6 as [Amount in LC_BW], @4 + @5 as Quantity_BW FROM YDIORCON.CSV (txt, codepage is 1252, no labels, delimiter is ',', msq); LEFT JOIN (TMP_BWTABLE) LOAD [Material Doc] as [Material Doc_BW], [Entered on] RESIDENT [ECC_TABLE];
ELSE //In place when model is reloaded for the first time and there's no history
[BW_TABLE]: LOAD @1 as [Material Doc_BW], @2 as [Mat Doc Year_BW], @3 as [Posting Date_BW], @4 as [Total Stock - Receipt], @5 as [Total Stock - Issue], @6 as [Amount in LC_BW], @4 + @5 as Quantity_BW FROM YDIORCON.CSV (txt, codepage is 1252, no labels, delimiter is ',', msq); LEFT JOIN (BW_TABLE) LOAD [Material Doc] as [Material Doc_BW], [Entered on] RESIDENT [ECC_TABLE];
ENDIF
STORE [BW_TABLE] INTO "History\BW_HISTORY.QVD" (qvd); //Store [BW_TABLE] for History
OK - I still don't really follow what you're trying to do and I'm sure your script can be optimised better by using Exists(). Also, instead of IF,THEN ELSE, maybe load the new data from your source, then set the ErrorMode to 0, then concatenate the QVD file then set the ErrorMode back to 1.
Anyway - as for the comparisions here's a guess as I'm not sure exactly what you want. It's the same as Henric's suggestion above really.
Load whole ECC data, then flag which BW records exist in ECC:
After your ECC load and before your BW load:
Map_MaterialDoc:
MAPPING LOAD
[Material Doc]
,1
RESIDENT ECC_TABLE;
Then, as an extra line in your BW load:
ApplyMap('Map_MaterialDoc',@1,0) AS ExistsInECC_Flag
.png "Former Employee"){kind=icon}
