I get very confused with synthetic keys and loops and things. I pull data from three different tables:
1. Account extract
2. Transaction Extracts
3. Application Extracts
I load the Account Extract first and load 'Account no as 'Account No',
then I load a transaction extract (which has an Account No field) WHERE EXISTS 'Account No').
This works perfectly. But when I make selections on my sheets that shows transactions it will not match back to my application data (as my application extract has no account number yet).
There is a field that I can use to match back, which has the same data, but the field has different names for all three extracts!
Application Extract - Company Code
Transaction Extract - Company
Application Extract - Company No
I need to be able to select a Company and look at its account holders' transactions, and then also go to a different sheet and look at all its applications.
I also have a synthetic field called 'Appl No' and 'Appl no' in my Account and Application extracts but when I use the WHERE EXISTS'Appl No') I get an error saying there is a loop in my file!
LOAD 'Account no' as 'Account no',
if('Company Code'=ASA','Ackermans SA', if('Company Code'='ANA','Ackermans NAM', if('Company Code'='HTSA','Hang Ten SA', if('Company Code'='HTNA','Hang Ten NAM', if('Company Code'='JJSA','Jay Jays SA', if('Company Code'='JJNA','Jay Jays NAM', if('Company Code'='SCSA','Shoe City SA' , if('Company Code'='SCNA','Shoe City NAM')))))))) as 'Company Code',
LOAD if(Selection does not match='SCSA','Shoe City SA', if(Selection does not match='HTSA','Hang Ten SA', if(Selection does not match='ASA','Ackermans SA', if(Selection does not match='ANA','Ackermans NAM' )))) as Selection does not match,
WHERE (Selection does not match>4);