Hello to all,
I'm facing some issues to join some tables together.
I have the following tables:
| [Main Data]: |
| Customer Code | Customer Description |
| 1234 | xxxxxx |
| 12345 | yyyyyy |
| 12346 | zzzzz |
| 12347 | wwww |
| 12348 | ppppp |
|
|
| [BudgetData]: |
| Customer Code | Customer Description |
| zwert | qqqqqq |
| 12345 | yyyyyy |
| ishdjoakd | lllllllllllll |
| 12347 | wwaww |
| gsdygsau | papapap |
I need to concatenate those, in order to have both information in my customer information, but considering the following condition:
- if I have the same customer code in both tables, I need the information for [Customer Description] to be loaded from [Main Data].
I tried to do this:
- Concatenate both information for [Customer Code].
- Use left join for both datasets.
Sample Script:
NoConcatenate
Customer_CONCAT:
LOAD
DISTINCT
NUM(REPLACE(REPLACE([Customer Code],'CP',''),'CROD','')) as [CorpCustomerCode]
Resident [Main Data];
Concatenate
Customer_CONCAT2:
LOAD
DISTINCT
[Customer Code] as [CorpCustomerCode]
FROM
$(vFOLDER)
(qvd);
DROP TABLE Customer_CONCAT2;
//exit script;
LEFT JOIN [Customer_CONCAT]:
CustomerData_TEMP:
LOAD
DISTINCT
NUM(REPLACE(REPLACE([Customer Code],'CP',''),'CROD','')) as [CorpCustomerCode],
[Customer] as CustomerDescription,
NUM(REPLACE([Corp/Non Corp Customer Code],'CP','')) as CCode,
[Corp/Non Corp Customer] as CCDescription,
[Customer Type Description] as CTDescription
Resident [Main Data];
LEFT JOIN [Customer_CONCAT]:
CustomerDataBudget:
LOAD
// [Customer Group] as CustomerGroup, //MA: We do not really need this information.
[Customer Code] as [CorpCustomerCode],
[Customer Name] as CustomerDescription,
[Corp/Non Corp Code] as CCode,
[Corp/Non Corp Name] as CCDescription,
'External' as CTDescription
FROM
$(vFOLDER)
(qvd);
//exit script;
DROP FIELDs [Corp/Non Corp Customer],[Corp/Non Corp Customer Code],[Customer Type Description],[Customer Code],[Customer];
RENAME Fields CorpCustomerCode TO [Customer Code],CCDescription TO [Corp/Non Corp Customer] ,CTDescription TO [Customer Type Description],CCode TO [Corp/Non Corp Customer Code], CustomerDescription TO [Customer];
NoConcatenate
CustomerData:
LOAD
*
Resident Customer_CONCAT;
DROP TABLE Customer_CONCAT;
exit script;
End of sample script.
The result of this script is that I 'lost' my [Customer Code] that only exist in my [BudgetData].
Any ideas on how to solve this issue?
I thought something like:
Load both datasets for [Customer Code], then concatenate those datasets. After that, include a left join with [Main Data] then for the 'missing' information (the information that I have only in [BudgetData], I would make a left join). I tried to use the 'where' statement, but it didn't worked.
Thanks for the support in advance.
Let me know if you need more information.
Kind regards,
Marco Arruda
