2 Replies Latest reply: Mar 5, 2013 5:06 AM by Aio Aio

# Pivot table on disjoined field

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

See attached qvw.

• ###### Re: Pivot table on disjoined field

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.