Excel Query: Calculation column using two columns?

[natheplas]

Hi,

I'm using excel 2016 query linked to a database.

I wanted to know if it's possible to get the query to have a calculation column based on two other columns in the query?

For example column 6 is 'QTY_ORDER' and column 7 is 'QTY_DELIVERED'. Is it possible to make column 8 'QTY_ORDER-QTY_DELIVERED'?

If so, how do I do this?

Thanks,

 

[natheplas]

Hi All, Can anyone help with the above please? I know once I return the data I can add a manual column to do this, but I wanted it in the query.

The issue Im having is I want to not include any 0 lines and if I leave the filter on, it then doesn't include newer numbers as there is a filter on.

So if I do the calculation column on the query I can say, 'not include 0'.

Thanks.

 

[SpillerBD]

In Get and Transform, yes. Add a custom column with a simple addition formula.

Are you using the Query to filter or are you filtering in the Query results table?

 

[natheplas]

Hi SpillerBD, thanks for coming back to me.

Where is the 'Get and Transform' within Microsoft Query?

https://www.google.co.uk/search?q=m...UICygC&biw=1920&bih=989#imgrc=_8dJrqu_q6i3fM:

How do you add the custom formula? Sorry I know it must be obvious, but I cant work it out from the screen ive sent across.

Im using the filter within the queried results. Can you filter within the screen above as well?

Thanks for this.

 

[SpillerBD]

Get n transform is new for 2016. It is included in all versions unlike the same feature set found in 2010 thru 2013 as Power Query, which required a Professional Plus version. So first hurdle, do you even have access to either one?

Check Mike Girvin's playlist on the subject https://www.youtube.com/watch?v=ohGFPF12Qwc&list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK

Everybody loves Mike.

 

[Fazza]

just add the field in the query's SQL

so if you have
SELECT field_1, field_2, and_another
FROM table

then to add a new field
SELECT field_1, field_2, and_another, QTY_ORDER - QTY_DELIVERED AS [Name of new field]
FROM table

if there are any functions like MAX, MIN, SUM, AVG, etc and you have a GROUP BY clause, also add the new field there

And if you want to exclude zero values, have a clause something like
WHERE QTY_ORDER - QTY_DELIVERED <> 0

 

[natheplas]

Hi Fazza,

Thank you so much for coming back to me! I'm not familiar with using SQL. I've got the following code below:

How do I use the below to add another column with WHERE QTY_ORDER - QTY_DELIVERED <> 0?

As in how and where do I put that on the sql statement?

Thank you so much!


SELECT SALES_ORDER.ORDER_NUMBER AS 'Sales Order Number', SALES_ORDER.DESPATCH_DATE AS 'Due Date', SALES_ORDER.ACCOUNT_REF AS 'Account Ref', SALES_ORDER.NAME AS 'Account Name', SOP_ITEM.STOCK_CODE AS 'Stock Code', SOP_ITEM.DESCRIPTION AS 'Stock Code Description', SOP_ITEM.QTY_ORDER AS 'Qty On Order', SOP_ITEM.QTY_DELIVERED AS 'Qty Delivered', SALES_ORDER.ALLOCATED_STATUS AS 'Cancellation Status', SALES_ORDER.DESPATCH_STATUS AS 'Despatch Status'
FROM SALES_ORDER SALES_ORDER, SOP_ITEM SOP_ITEM
WHERE SOP_ITEM.ORDER_NUMBER = SALES_ORDER.ORDER_NUMBER AND ((SALES_ORDER.DESPATCH_DATE<?) AND (SALES_ORDER.DESPATCH_STATUS<>?) AND (SALES_ORDER.ALLOCATED_STATUS<>?) AND (SALES_ORDER.ANALYSIS_1<>?))

 

[Fazza]

add it to the end of the current WHERE clause - the last line

it is now
WHERE SOP_ITEM.ORDER_NUMBER = SALES_ORDER.ORDER_NUMBER AND ((SALES_ORDER.DESPATCH_DATE?) AND (SALES_ORDER.ALLOCATED_STATUS<>?) AND (SALES_ORDER.ANALYSIS_1<>?))

make it
WHERE SOP_ITEM.ORDER_NUMBER = SALES_ORDER.ORDER_NUMBER AND ((SALES_ORDER.DESPATCH_DATE?) AND (SALES_ORDER.ALLOCATED_STATUS<>?) AND (SALES_ORDER.ANALYSIS_1<>?)) AND QTY_ORDER - QTY_DELIVERED <> 0

 

[Fazza]

I was in a big rush when I posted & didn't read the SQL. Just dumbly appended the expected text to what you'd posted. In fact, it also needs to reference the table.

So the WHERE clause should not be what I posted earlier, rather it should be as below - now with the table name added to explicitly get the reference correct. The SQL is just text - simply edit it to change it.

WHERE SOP_ITEM.ORDER_NUMBER = SALES_ORDER.ORDER_NUMBER AND ((SALES_ORDER.DESPATCH_DATE?) AND (SALES_ORDER.ALLOCATED_STATUS<>?) AND (SALES_ORDER.ANALYSIS_1<>?)) AND SOP_ITEM.QTY_ORDER - SOP_ITEM.QTY_DELIVERED <> 0