Is upgrading to Office 2013 Pro the answer to my memory problems?

[masplin]

Hi

I have 64-bit 16GB Ram machine that is constantly running out of memory on a big messy project I'm working on. The underlying data is coming form software written in Pascal and it a right told mess!!! i am having to do an enormous amount of filter calculation which I assume where the problem lies. I have read that the new Office 2013 is a massive improvement specifically in this area.

I was thinking I had to buy a much more powerful PC, but sounds like I should be trying the Office 2013 version first. I understand as an at home consultant this is going to be tricky as i have no interest in paying £10 a month for Office 365. So the question is could I go down the volume licensing route mentioned in another post even though I am based in the UK? Has anything changed on how a lowly individual can get hold of this?

Thanks for any advice

Mike

 

[masplin]

Hi Guys. Sorry been on holiday hence reason for silence. I don't want to waste too much of your time but could you give me a steer on how big a job i'm looking at. My big problem is there is no "customerIdentifier" and I'm strugglingto think of how to make one. However couple of strategic questions. I did the select distinct for the invoice static data and below is a typical output.

<tbody>
[TD="align: left"] InvNum [/TD]
[TD="width: 90, bgcolor: #FFFFFF, align: left"] RegNum [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Salut [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Surname [/TD]
[TD="width: 159, bgcolor: #FFFFFF, align: left"] ADDR1 [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] ADDR2 [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] ADDR3 [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Postcode [/TD]
[TD="width: 129, bgcolor: #FFFFFF, align: left"] Mobile [/TD]
[TD="width: 174, bgcolor: #FFFFFF, align: left"] Email [/TD]

[TD="width: 84, bgcolor: #FFFFFF, align: right"] 123456 [/TD]
[TD="width: 90, bgcolor: #FFFFFF, align: left"] OE08 DYO [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Mrs [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Smith [/TD]
[TD="width: 159, bgcolor: #FFFFFF, align: left"] Flat 15, 1 Jones Ave [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Stoke [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: right"][/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] ST1 4RQ [/TD]
[TD="width: 129, bgcolor: #FFFFFF, align: left"] 07901 533985 [/TD]
[TD="width: 174, bgcolor: #FFFFFF, align: left"] sally.smith@gmail.com [/TD]

[TD="width: 84, bgcolor: #FFFFFF, align: right"] 123457 [/TD]
[TD="width: 90, bgcolor: #FFFFFF, align: left"] OE08 DYO [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Mr [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Smith [/TD]
[TD="width: 159, bgcolor: #FFFFFF, align: left"] Flat 15 1 Jones Ave [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Stoke [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: right"][/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] ST1 4RQ [/TD]
[TD="width: 129, bgcolor: #FFFFFF, align: left"] 07977 659867 [/TD]
[TD="width: 174, bgcolor: #FFFFFF, align: left"] tim.smith@gmail.com [/TD]

[TD="width: 84, bgcolor: #FFFFFF, align: right"] 123458 [/TD]
[TD="width: 90, bgcolor: #FFFFFF, align: left"] OE08 DYO [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] , [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Smith [/TD]
[TD="width: 159, bgcolor: #FFFFFF, align: left"] Flat 15, 1 Jones Ave [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Stoke [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: right"][/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] ST14RQ [/TD]
[TD="width: 129, bgcolor: #FFFFFF, align: right"] 7901533985 [/TD]
[TD="width: 174, bgcolor: #FFFFFF, align: left"] sally.smith@gmail.com [/TD]

[TD="width: 84, bgcolor: #FFFFFF, align: right"] 123459 [/TD]
[TD="width: 90, bgcolor: #FFFFFF, align: left"] OE08 DYO [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Mrs [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] ? [/TD]
[TD="width: 159, bgcolor: #FFFFFF, align: left"] ,, [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] Stoke [/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: right"][/TD]
[TD="width: 84, bgcolor: #FFFFFF, align: left"] ST1 4RQ [/TD]
[TD="width: 129, bgcolor: #FFFFFF, align: left"] 07901 533985 [/TD]
[TD="width: 174, bgcolor: #FFFFFF, align: left"] sally.smith.gmail.com [/TD]

</tbody>

As you can see data is sometimes incomplete (blanks or spacial characters), invalid (no @ in email) or just different (use of comma or spaces). We do not always have the car reg either. It is obvious to the human eye this is one household with 2 people bringing in the same vehicle. So I have 2 strategic questions:

1. I there a straight forward way of validating data before importing i.e. length of field, contains certain characters, only contains letter or numbers or letters&numbers but not special characters? Currently I import as is and create "clean" calculated column for every field i.e. doubling the number of columns.
2. It seems that the logic to interpret this data as one household of 2 unique customers would be extremely hard to code. Maybe this is easier to achieve in SQL than I imagine?

Clearly we need to improve the quality of the data capture, but we have no control over the level of validation at the input stage. We are looking at changing system by the end of the year, but in the short term I have to work with what we've got. The question is what sort of level of investment in time will I need to make to firstly learn the skills and implement a solution, and how good could it realisitcally be if the raw data is as above? Basically do I accept I see 4 unique customers with the downside of undervlauing them and also overmarketing to them.

Thanks for any advice before I jump in at the deep end

Mike

 

[prez02]

Most SQL-dialects have text functions like left(), right(), substring(), length() you could use to cleanup column values or combine them, and case to check whether certain criteria are met.

It is not that difficult, instead of

Select table.column

you have to write eg.

Select left(table.column,4) as Firstfourletters.

I use it a lot instead of calculated columns in Powerpivot.

We have a lot od duplicate customers in our company database as well. For us, the solution was too manually maintain a separate database in Excel, which we use for malings, but that required some work done manually, even after using an algorithm to detect duplicates. So it really depends on what you want to do with the data, and whether it is worth the effort?

 

[masplin]

So SQL has similar functions to excel. Is there any thing to ask "is this a letter" as opposed to "is this text" so i can test if a cell contains "?" or "/" for example? Can you right an if statemnet e.g. select if(len(table.column)=7,table.column,blank()) ish?

Yes its really for marketing purposes so similar to yours. If the records were complete and just duplicates it would be relatively stragiht forward. the problem is i have no idea if a customer has visited us 6 timnes what to expect in terms of static data. My 2 objectives are:
1. understand the "value" of a customer so that means including all their transactions
2. Market to them based on their previous transactions and where possible have access to mailing, email and sms (so combining if entries are not complete)

Failing to idenfiy unique customers means we will send conflicting marketing messages multiple times to the same person. So probably we need to maintain a unique customer DB as you have outside of the system. Maybe that is actually easier.

thanks

Mike

 

[prez02]

A basic SQL statement tends to have three parts:
(1) SELECT columns (2) FROM table(s) (3) WHERE filter condition(s).

There is no if-clause, but a case function which lets you do the same.

SELECT
table.column,
CASE table.column
WHEN len(table.column) = 7
THEN "Hooray"
ELSE "Boo"
End,
table.column,
table column,
....
FROM table
WHERE year(table.date)>2010

That said, I am not a SQL experts (and this is a Excel/Powerpivot forum ) but if you look around on the web, there is a lot of information about SQL around. Just beware, there a different dialects like MySQL, T-SQL etc, which differ slightly. The joining of two or more tables in the FROM clause is what is more difficult to grasp, just ignore it, you do not have to use it in your case.

I would try and base my analysis around the car registration number. Every car has to have one, it is unique, everybody is willing to give this information to you, it is relatively short, which could mean less mistakes and a greater likelyhood that it will captured all the time, or at least the easiest you could get people to capture.

An email adress could also be useful, but not eveverybody has one, or is willing to give it to you. Then you could check what information you have about the owner(s). they have moved house, or maybe several people living there.

You could to this by just creating a table that ownly consists of the different or distinct regnums, and then create a relationship to your other table, so you could see all the information you have about one car. The only thing you would miss this way, is people changing cars, or having two,. It is really about to have to decide what is worse, writing two letters to one and the same customer or writing none at all.
Hope this helps a bit,
Carsten

 

[masplin]

Thanks Carsten. I'm going ot do a bit of research into the data to find out what sort of quality I have for different fields. As you say regnuym woudl be pretty perfect if highly complete. Thnask for your help

Mike

 

[masplin]

stupid question deleted.

 

[masplin]

I'm obviously quite stupid as cannot work out how to get data from table 1 to table 3. as suggested I have removed the static data from the transactions and created an invoice table containing it. This reduces my columns enormously and removes lots of duplicated data. I assume I am doing what was suggested.

Table 1: Multiple transactions under unique Invoice numbers (500k) (each invoice may have several transactions)
Table 2: Unique invoices each with a customer code (150k) (each customer code may have several invoices)
Table 3: Unique Customer code (80k)

Table 1 has a relationship with table 2 via INVNUM (unique in table 2)
Table2 has a relationship to Table3 via "Customer Code" (unique in Table 3)

For each customer in Table 3 I want a calculated column for count of transactions, but cannot work out the syntax. Each customer code is related to several INVNUM which in turn are related to several transactions.

Previously I had the unique customer code on both transaction and customer table so could filter like this

=CALCULATE(COUNTROWS(Transactions),filter(Transactions,Transactions[Customer Code]=Customers[Customer Code] 
)

Now the link is through the Invoice table and i am mystified. I've tried putting RELATED in every combination I can think of with no luck. thanks for any advice.

Mike