Matching tables

[gerhardl]

Hi,

I'm still busy figuring QV out, and would appreciate some help with the following:

I recently sent a marketing text to about 40,000 customers. In QV I loaded (from excel) the following fields:

• Account No
• Cell phone
• Country
• Title
• First Name
• Last Name
• Available Credit
• Date SMS Sent

Now, what I want to do is measure these customers' purchasing behaviour. I have a daily "transaction extract", which is a delimited text file with the following fields:

• Account No
• Posted Date
• Effective Date
• Store No
• Merchant Code
• Store Name
• Store Country
• Transaction Type
• Transaction Code
• Transaction Description
• Transaction Amount

I want to upload this file daily, but only where [Tran Code] = 35 or 36.

Then I want to be able to measure how many of the 40,000 customers made a purchase, their values, etc. Remember that the transaction file will also include Account Numbers that are NOT in the SMS List.

Eventually I want to be able to compare the SMS customers' behavious before AND after the message was sent, so I would have tables like "% of SMS customers spending", "total turnover" etc. all before Vs. After.

Currently I get stuck when importing the transaction files - I do not know whether they should be joined, concatenated, or what - and I don't know how to mape the fields to each other.

Please help!! If I need to give sample data, please explain to me what you need - I can't legally share this information as it is, so I will have to manipulate it first, but of course some account numbers must still match.

[vgutkovsky]

I'm going to call your first table Table1 and your transaction extract Table2. You would load Table1 as usual. Include the following WHERE clause for the load of Table2:

WHERE ([Trans Code]=35 or [Trans Code]=36) and exists([Account No])

This will ensure you are only loading in relevant transactions for those customers that exist in Table1. You can then do counts/sums/etc in your charts as usual.

Regards,

[gerhardl]

Hi,

I get an error message "garbage after statement" when the script execution gets to "table2".

When I enter the WHERE clause the FROM statement changes from blue to normal black font, so not sure what I'm doing wrong... Tried putting the where clause at the start, end and middle of the load of table 2 - no luck.

Here is my script:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='R #,##0.00;R-#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD [Cell phone],
[Account No],
Title,
[First name],
[Last Name],
OTB,
Country,
[Date SMS Sent]
FROM

(ooxml, embedded labels, table is Sheet1);

LOAD
Company,
Product,
[Account No],
[Posted Date],
[Effective Date],
[Store No],
[Merchant Code],
[Store Name],
[Store Country],
[Card No],
[Transaction Type],
[Tran Code],
[Trans Desc],
[Trans Amount],
[Trans Auth]
WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No]),
FROM

(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines);

[#4 · Accepted solution]

Use Where() after From().

LOAD
Company,
Product,
[Account No],
[Posted Date],
[Effective Date],
[Store No],
[Merchant Code],
[Store Name],
[Store Country],
[Card No],
[Transaction Type],
[Tran Code],
[Trans Desc],
[Trans Amount],
[Trans Auth]
FROM

(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)

WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No])

;