How do I alter existing data?

Fri, 01 Apr 2011 21:52:58 GMT

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

How do I alter existing data?

disqr_rm — Sat, 02 Apr 2011 01:49:27 GMT

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.

topic How do I alter existing data? in QlikView

How do I alter existing data?

How do I alter existing data?