Combining data from different tables.

Mon, 26 Mar 2012 21:03:58 GMT

I request you to please help me with the following issue. I have 3 tables. First one gets the underlying data from database, however the amounts are calculated expressions. Second one comes from Excel. In Table 3, I have to display the sum from Table1 and Table 2 ( which are actually 2 pivot tables). I cloned Table1 as Table3 and added the expression from Table2 to get the total, but it is not coming correctly. with the help from this forum, I am able to complete the tables 1 and 2. But I am unable to get table 3.

TABLE1

		Product2	Product3	Product4
Hierarchy1	Hierarchy2
abc	lmn	201.00	301.00	401.00
def	opq	201.00	301.00	401.00
ghi	rst	201.00	301.00	401.00
null		3.00	3.00	3.00
TOTAL		603.00	903.00	1203.00

Notice that in table 1..null is ignored and those values are added to other products. That row is not shown.

Table 2

		Product4
Hierarchy1	Blankfield
xyz1		10
xyz2		20
xyz3		30 (ignore)
TOTAL		30

Table 3

		Product2	Product3	Product4
GrandTotal	Blankfield	603.00	903.0	1233.00

Also, one more issue is : When I have a cross tab, (Pivot table), I dont want the headers for the fields on the left side. For example, I want to hide the headers for Hierarchy1 and Hierarchy2. How can I supress the header ?

Re: Combining data from different tables.

Mon, 26 Mar 2012 22:00:16 GMT

For this situation i would usually use concatenate.

For example:

Table1:

LOAD * INLINE

[

Heirarchy1, Heirarchy2, Product2, Product3, Product4

abc, lmn, 201.00, 301.00, 401.00

def, opq, 201.00, 301.00, 401.00

ghi, rst, 201.00, 301.00, 401.00];

Concatenate

LOAD * INLINE

[

Heirarchy1, Product4

xyz1, 10

xyz2, 20

];

Total:

LOAD

'Grand Total' as Column1,

'Blankfield' as Column2,

sum(Product2) as Product2,

sum(Product3) as Product3,

sum(Product4) as Product4

Resident Table1;

Drop table Table1;

Now the table total will look a lot like the Table 3 you are wanting as a result.

I also have attached a working .qvw for reference.

Hope this helps!

topic Re: Combining data from different tables. in QlikView

Combining data from different tables.

Re: Combining data from different tables.