Combine Table Summaries into One Record

[jmonroe918]

I am loading summed data from two separate sources (access queries). Each one gives me a total sum of a field from each (acc qty and rej qty). See the load script below.

I am trying to "merge" (or aggregate) the two sum totals into one table with one record listing each sum total. What I am getting is one table with two records:

     Total Qty Acc     Total Qty rej

     56,603, 439          0

     0                         229,685

How can I get these two one-field tables joined into one table, with two fields and one record?

    Total Qty Acc     Total Qty rej 

     56,603, 439          229,685

I will then add a column where I calculate the DPPM ([rej/acc] * 10000000) where I will then look the figure up in an In-Line table to get the Sigma value added to another column (via Interval Match).

But I need the sing table first....

The qvw is attached as well.

Thanks.

Jeff

LOAD

     num(Sum([No_Approved Parts]), '#,##0') as [Total Qty Acc];

SQL SELECT *
FROM qryPOReport;

Concatenate (Temp)
LOAD

     num(Sum([Rej Qty]), '#,##0') as [Total Qty Rej];

SQL SELECT *
FROM qryNCMR
WHERE Division In ('TJM', 'VST')
and Type = 'Vendor';

[its_anandrjs]

Hi,

In place of concatenation i suggest use variables

like

vTotalQtyAcc and vTotalQtyRej

and use in your formula like 

($(vTotalQtyRej) / $(vTotalQtyAcc)] * 10000000)

Hope this helps

Thanks & Regards

[rwunderlich · Accepted solution]

Use

JOIN (temp)

instead of

Concatenate (Temp)

-Rob

http://robwunderlich.com

[its_anandrjs]

Hi,

You can create two variables in your load script like below

Let vTotalQtyAcc = Peek( 'Total Qty Acc', 0, 'Temp' ) ;

Let vTotalQtyRej = Peek( 'Total Qty Rej', -1, 'Temp' ) ;

and use like this

($(vTotalQtyRej) / $(vTotalQtyAcc)] * 10000000)

When ever data is updated in your table your variables refresh and also you can use join in place of the concate

Hope this helps

Thanks & Regards

[jmonroe918]

Exactly what I was looking for.

Thanks Rob.

Jeff