3.0 Query Builder - Where clause when using same Feild twice

Toad Data Point & Toad Intelligence Central
1

Hi,

I have a single table which holds transactions for different areas of the business. The relevant fields are:

Business_Unit (Unique Identifier)
Subsidiary (Cost Codes) (4999 = recovered amount)
Actual_Amount (Currency Value)

What I’m after is a four column summary

Business_Unit (Group by)
TotalSpend (Sum of Actual_Amount where Subsidiary not equal to 4999)
Recovered (Sum of Actual_Amount where Subsidiary equal to 4999)
Difference (TotalSpend subtract Recovered)

Using TDA 3.0 I have added the Business_Unit field and select Group(1). Then I have added the Actual_Amount field twice and tried to create Where condition formula.

Formula for TotalSpend = AutoRange_Sheet1.Subsidiary <> ‘4999’
Formula for Recovered = AutoRange_Sheet1.Subsidiary = ‘4999’

Only one where formula at a time will work if I try to include both the result are blank

Do I need to use the Subquery… button in the Where condition window?

Any help would be most appreciated.

Regards

David

2

, SUM(JDE_Extract.Actual_Amount <> 4999) AS TotalSpend ,

, SUM(JDE_Extract.Actual_Amount <> 4999) - Sum(JDE_Extract.Actual_Amount = 4999) AS Difference

FROM JDE_Extract JDE_Extract

GROUP BY JDE_Extract.Business_Unit

See this post on how to build a calculated field.

http://tda.inside.quest.com/thread.jspa?threadID=28972

Debbie

You should be able to do this as calculated fields. Try this query. I have attached tsm file.

SELECT JDE_Extract.Business_UnitSum(JDE_Extract.Actual_Amount = 4999) AS Recovered
Query.tsm (46.6 KB)