topic Re: Combine Table Summaries into One Record in QlikView

Combine Table Summaries into One Record

jmonroe918 — Thu, 27 Mar 2014 20:11:11 GMT

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';

Re: Combine Table Summaries into One Record

its_anandrjs — Thu, 27 Mar 2014 20:28:19 GMT

Hi,

In place of concatenation i suggest use variables

vTotalQtyAcc and vTotalQtyRej

and use in your formula like

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

Hope this helps

Thanks & Regards

Re: Combine Table Summaries into One Record

rwunderlich — Thu, 27 Mar 2014 20:36:10 GMT

Use

JOIN (temp)

instead of

Concatenate (Temp)

-Rob

http://robwunderlich.com

Re: Combine Table Summaries into One Record

its_anandrjs — Thu, 27 Mar 2014 20:38:10 GMT

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

Re: Combine Table Summaries into One Record

jmonroe918 — Thu, 27 Mar 2014 21:55:55 GMT

Exactly what I was looking for.

Thanks Rob.

Jeff