Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following data about our customers:
LOAD
Web_CustomerId,
Web_CustomerId as Web_CustomerIdCountable,
Web_CustomerId + 73081 as Web_CustomerId_hash,
Web_FirstName,
Web_LastName,
Web_CivicNumber,
Web_EMail,
Web_GroupId,
Web_CustomerStatusId,
Web_ChannelId,
Web_AccountCreated,
Web_Age,
Web_CustomerOrderStatusId,
date(floor(num(Web_FirstOrderDateTime)), 'YYYY-MM-DD') as Web_FirstOrderDateTime,
date(floor(num(Web_LastOrderDateTime)), 'YYYY-MM-DD') as Web_LastOrderDateTime,
Web_IsCompany,
Web_IsEmailForbidden,
//Default logic for newsletters goes here
To this data I want to add which types of newsletters the customer would like to receive.
I.e.
Regular newsletter
Offers based on prior purchases
Vouchers and freight free offers
Erotic newsletter
This data I have in a separate file as follows
left join(Web_Customer)
LOAD Web_CustomerId,
if(Web_SubscriptionMediaId = 1, Web_SubscriptionMediaId) as SubscriptionType_Newsletter,
if(Web_SubscriptionMediaId = 2, Web_SubscriptionMediaId) as SubscriptionType_Offer,
if(Web_SubscriptionMediaId = 3, Web_SubscriptionMediaId) as SubscriptionType_Voucher,
if(Web_SubscriptionMediaId = 4, Web_SubscriptionMediaId) as SubscriptionType_Erotic,
Web_SubscriptionChannelId,
if(Web_SubscriptionMediaId = 1, Web_IsActive) as IsActive_Newsletter,
if(Web_SubscriptionMediaId = 2, Web_IsActive) as IsActive_Offer,
if(Web_SubscriptionMediaId = 3, Web_IsActive) as IsActive_Voucher,
if(Web_SubscriptionMediaId = 4, Web_IsActive) as IsActive_Erotic
FROM $(include=pathWeb.txt)Web_SubscriptionMatrix.csv
My problem is that not all customers are in the file called Web_SubscriptionMatrix.
Customers that are not in the file have to fall back to a default setting and this is where I run into problems.
I figure the default settings for each customer should be created in the initial load of Web_Customer (the logic for this is not in the LOAD statment above) and then those customers that are in the Web_SubscriptionMatrix file needs to be updated in the Web_Customer table.
The problem that I have run into is that I have not found a way to alter the information in the Web_Customer table once it has been loaded.
I have tried using add load and done a partial reload bur with no success.
Does anyone have any ideás on how to solve this?
br
Martin
One idea would be to load Web_SubscriptionMatrix.csv first and then left join with your Customer master file.
So something like:
Customers:
LOAD Web_CustomerId,
Web_CustomerId as tmp_id,
..........
from Web_SubscriptionMatrix.csv;
Left join (Customers)
LOAD ... from CustomerMasterFile;
----
So now you have complete records for all the customer which have an entry in Web_SubscriptionMatrix.csv file.
Now using CONCATENATE LOAD and NOT Exists() functions you could load rest of the Customers from CustomerMaster, hard coding Newsletter, etc fields. Something like:
CONCATENATE(Customer)
LOAD Web_CustomerId,
Web_CustomerId as tmp_Id,
.......
'Regular Newsletter' as SubscriptionType_Newsletter,
.......
FROM CustomerMasterFile
WHERE Not Exists (tmp_Id, Web_CustomerId);
DROP Field tmp_Id;
Hope this helps.
One idea would be to load Web_SubscriptionMatrix.csv first and then left join with your Customer master file.
So something like:
Customers:
LOAD Web_CustomerId,
Web_CustomerId as tmp_id,
..........
from Web_SubscriptionMatrix.csv;
Left join (Customers)
LOAD ... from CustomerMasterFile;
----
So now you have complete records for all the customer which have an entry in Web_SubscriptionMatrix.csv file.
Now using CONCATENATE LOAD and NOT Exists() functions you could load rest of the Customers from CustomerMaster, hard coding Newsletter, etc fields. Something like:
CONCATENATE(Customer)
LOAD Web_CustomerId,
Web_CustomerId as tmp_Id,
.......
'Regular Newsletter' as SubscriptionType_Newsletter,
.......
FROM CustomerMasterFile
WHERE Not Exists (tmp_Id, Web_CustomerId);
DROP Field tmp_Id;
Hope this helps.