Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Marco, if it helps, you can check previously laoded customers using Exists() function:
[Main Data]:
LOAD [Customer Code],
[Customer Description]
...
FROM...
//[BudgetData]:
Concatenate
LOAD [Customer Code],
[Customer Description]
FROM... Where Not Exists([Customer Code])
Then you have a table without duplicates from budget and you can join the remaining data.
Thanks, I'll try this way to see how it goes.