topic How do you union 2 qlikview tables in QlikView

How do you union 2 qlikview tables

robert_young_hc — Fri, 03 Jun 2011 16:20:36 GMT

Hi Everyone,

Is there a way to union 2 tables when creating a chart. I currently have one table with a series of categories and another with a different series. I need to create a chart that will have a union of these tables. I know I could create a new table during the load but am trying to avoid this due to size of the current app and how much data I would be duplicating. Below is an example of the 2 different tables and what I am trying to archive. Please note that the structure has been simplified to demonstrate my example.

Table A

Project Area Product Category Time

AAA A AAAAA A 10

AAA A AAAAA C 7

AAA A BBBBB A 3

AAA A AAAAA B 8

AAA A CCCCC C 10

AAA B BBBBB A 10

AAA B BBBBB C 17

AAA B CCCCC A 10

AAA B CCCCC B 24

AAA B DDDDD C 15

Table B - This is category W

Project Area Time

AAA A 20

AAA B 32

AAA B 19

Chart would look like

Project Area Category Time

AAA A A 13

AAA A B 8

AAA A C 17

AAA A W 20

AAA B A 20

AAA B B 24

AAA B C 32

AAA B W 51

Any suggestions other than during the load.

How do you union 2 qlikview tables

johnw — Fri, 03 Jun 2011 16:53:13 GMT

If you were to concatenate Table B onto Table A while adding a Category field with value W for the Table B data, where is the duplication? Also, even when there is duplication or denormalization, this isn't typically a problem for QlikView due to its compression.

Re: How do you union 2 qlikview tables

Fri, 03 Jun 2011 16:58:17 GMT

Hi, see this example.

How do you union 2 qlikview tables

Fri, 03 Jun 2011 17:06:07 GMT

Hi Robert,

If you had the following load statements:

TableA:
load * inline
[
Project,Area,Product,Category,Time
AAA,    A,   AAAAA, A, 10
AAA,    A,   AAAAA, C, 7
AAA,    A,   BBBBB, A, 3
AAA,    A,   AAAAA, B, 8
AAA,    A,   CCCCC, C, 10
AAA,    B,   BBBBB, A, 10
AAA,    B,   BBBBB, C, 17
AAA,    B,   CCCCC, A, 10
AAA,    B,   CCCCC, B, 24
AAA,    B,   DDDDD, C, 15
];
TableB:
load * inline
[
Project,Area,Time
AAA,    A,   20
AAA,    B,   32
AAA,    B,   19
];

You could build a straight table with the following dimensions:

Project
Area
=if(isnull(Category), 'W', Category) calculated dimension

Expression:

sum([Time])

That should give you the desired result without having to perform a reloading.

How do you union 2 qlikview tables

johnw — Fri, 03 Jun 2011 17:35:05 GMT

Ecorrea, we'd only want your Table_Result table if we really ARE trying to get rid of the Product dimension completely. I'd guess we still want the dimension, just not in the chart. I agree with concatenating Table_B, however, which is what I was suggesting.

QueueWeRavn, I can't think of any good reason to leave these tables separated in the model. And if there IS a good reason (not evident from the fields we were given), I can't think of any good reason not to add the Category field to Table B during the load. Calculated dimensions tend to be slower than real dimensions, plus you then can't do things like select category W, though in some cases it IS an advantage to have a solution that doesn't require a reload, so good job there. Note also that we'll get a synthetic key either way. This isn't strictly a problem, but IS a hint that we should perhaps combine the tables.