topic Re: Pivot table on disjoined field in QlikView

Pivot table on disjoined field

Mon, 04 Mar 2013 16:15:42 GMT

Hi everybody,

I got two tables:

Table1:

Who	where	count
A	pl_1	4
A	pl_1	3
A	pl_2	8
B	pl_1	5

Table2:

who	where	frequency
A	pl_1	10
A	pl_3	5
B	pl_1	20

the results should be a pivot table with:

who	where	sum(count)	?Function?(FREQUENCY)	other expressions based on A
A	pl_1	7	10	…
A	pl_2	8	0	…
A	pl_3	0	5	…
total A		15	15	…
B	pl_1	5	20	…
total B		5	20	…

If I use a left join between the table A and table B, my frequency will be wrongly a sum (ex a;Pl_1;20 instead of 10, caz in order to have the total frequency, I should use SUM(b.frequency)).

In both cases (left join and join) I got no A;Pl3.

I cannot understand:

1) what should be the ?function? used for the frequency (Expressions)

2)How could I join the two table to have the A;Pl_3 in the pivot (Scripting)

Saying "other expressions based on A" I 'd like you to understand the other expressions of the pivot table are quite complex and referring only to table A.

Can you help me?

Re: Pivot table on disjoined field

Gysbert_Wassenaar — Mon, 04 Mar 2013 17:05:41 GMT

See attached qvw.

Re: Pivot table on disjoined field

Tue, 05 Mar 2013 10:06:48 GMT

thank you for your answer.

I understood the problem was different.

In my table1, I got a function like week(DATA) as week_grp, table2 joined with table1 also on DATA.

When I make a selection on week_grp the A;Pl_3 is kicked out.

Is there anything I can do in the join to avoid making week(DATA) also in table2?

it seems really weird to me, I thought functions like that would be considered just as attributes, and joining the table was enough to use them without replaying in the secondary table.