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]

Ah technical question!!! Can I do that within the powerpivot import as I have no access ot the SQL tables and have failed miserably to get the sfotware company to get the data in the format I need. We are a small cusotmer of theirs and they have said they will do it, but who knows when.

 

[Jacob Barnett]

If you have 2 tables called TableA and TableB your query could look something like:

select
columnA,
columnB,
columnC,
columnD

from dbo.TableA

Union

select
columnA,
columnB,
columnC,
columnD

from dbo.TableB

As long as the 2 queries have the same number of columns and each column has the same data then this construct should work. You could potentially introduce duplicates unintentional duplicates which you could avoid using two fields concatenated together as a key. Also worth looking up the difference between UNION and UNION ALL.

Hope this helps
Jacob

 

[masplin]

Unfortunately what I have is one table with 6 bits of data I need and another table with 12 bits of different data. The common link between them is they both have invoice numbers. So I'm having to do a calculate values where invoice match type lookup to get the data from table 1 into table 2.

 

[Jacob Barnett]

OK - so I take from this you are lacking the SQL know how to pull the 2 together. Depending on how your tables are set out this could well be extraordinarily simple!!

If you can post the table names and required columns I can write the query for you.

Jacob

 

[masplin]

Hi Jacob

yes sorry I'm completely untrianed and making this up as I go along!!! All I have worked out is how to connect to the DB and select a prexisting table. Does this mena I will lose all the work I've already done building measures based on the tabled pulled straight down?

 

[Jacob Barnett]

Not necessarily in that your new joined table will have all the same columns that your previous one did just with the extra info from table 2 integrated.

 

[masplin]

Hi Jacob

So the main table i'm using is called dbo.VW_InOutStock. Columns are:
Depot
Sysdate
customer
CTSalut
CTfirstN
CTSurname
CTFullname
Name1
ADDR11
ADDR12
ADDR13
ADDR14
PCODE1
PHONE1
CTPHONE
CTMOBILE
CTEMAIL
Group
INVNUM
INVDATE
STCODE
PRDGRP
SUMCODE
DESCRIPN
SALESN
STOCKN
VATTYPE
VATPC
QUANTITY
SELLPRICE
LINETOTAL
COST
LINECOST


The 2nd table I need to add is called custom.report3full. I'm not sure this is an sql tablns i need are
RType
TType
Description
RegNum
DVMake
MOTDue
ServiceDue

This table also contains the InvNum and all the above fields are duplicated in the multiple lines that make up one invoice. so I have been using a calculate(values(2nd table column),filter(2nd table, 1st table[invnum]=2ndtable[invnum]) to get them inot the first table as new columns.

I'm guess I go to existing connections and go to "write an sql query"

one hitch the 2nd table is updated every day, but the first table I got done once for me and have never been able ot get thme ot update it. It therefore is shorted and ends on 31/1/2013. Does this knacker up any union?

Thanks for your hlep

Mike

 

[Jacob Barnett]

Mike, a Union is basically where you join two identically structured data sets 'vertically' (i.e. one on top of the other). What seems to be required here is a simple join - the fact that one of your tables is stale isn't an issue as we can use a left join to ensure that we get every thing from the main table but you will obviously get some blanks for records where there is no data in the second table. What I don't see is a common column but assuming its invoice number as you mentioned earlier a query could look like:

select

[COLOR=#574123]IO.Depot,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]Sysdate,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]customer,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTSalut,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTfirstN,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTSurname,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]CTFullname,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]Name1,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR11,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR12,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR13,[/COLOR]
[COLOR=#574123]IO.[/COLOR][COLOR=#574123]ADDR14,
[/COLOR][COLOR=#574123]CU.RType,[/COLOR]
[COLOR=#574123]CU.TType,[/COLOR]
[COLOR=#574123]CU.Description,[/COLOR]
[COLOR=#574123]CU.RegNum[/COLOR][COLOR=#574123]
[/COLOR]
from [COLOR=#574123]dbo.VW_InOutStock IO
[/COLOR]left join dbo.[COLOR=#574123]custom.report3full CU on CU.invoicenumber = IO.invoicenumber [/COLOR]

Should be pretty obvious how the join itself works - the trick is aliasing the tables, I've used IO and CU but these could be anything. The aliases mean you can reference the second table.

Something like the above should give you the necessary single table!

Hope this helps.
Jacob

 

[masplin]

ah excellent - you learn something everyday whoich makes it worth getting up! Essentailly an invoice may contian up to 20 lines of transactions, but every line has the same INVNUM and the same data from table 2. So there is a 20 to 20 lookup and VALUES only works becuase the table retruned by the filter has the same value. As long as this just takes the INVNUM form Table 1 and looks up on TABLE 2 and takes the first match it should work fine. Will give it a go. Thanks

 

[masplin]

Before I could attempt this the software guys added the missing columns to the main table!!! Now I have the bizarre situation of removing al lthe filtered lookups has actually made the thing run like treacle!!!! so I'm now on Excel 2013 and using only 1 500,000 row table and seem to be worse off than before!!! Is there some clever way of cleaning Excel 2013 up as know there was some clever trick to force Powerpivot 2012 to sort of rebuild?