Sum count in pivot table

[Peony]

Hi all.

I have two tables and I need to count total sum by field "SqrFootage" for all five stores from table "Store" . 

In pivot table I write as expressions "=SUM(TOTAL SqrFootage)"

But as the result getting 1800 instead 4600. Where I am wrong?

Tables exemples:

SqrFootage:

LOAD * INLINE [

Store,SqrFootage

A,1000

B,800

];

Store:

LOAD * INLINE [

Store,Prod,Price,Date

A,1,$1.25,1/1/2006

A,2,$0.75,1/2/2006

A,3,$2.50,1/3/2006

B,1,$1.25,1/4/2006

B,2,$0.75,1/5/2006

];

[antoniotiman · Accepted solution]

Hi Nataliia,

may be this

SqrFootage:
Mapping
LOAD * INLINE [
Store,SqrFootage
A,1000
B,800];
Store:
LOAD *,ApplyMap('SqrFootage',Store) as SqrFootage INLINE [
Store,Prod,Price,Date
A,1,$1.25,1/1/2006
A,2,$0.75,1/2/2006
A,3,$2.50,1/3/2006
B,1,$1.25,1/4/2006
B,2,$0.75,1/5/2006];

Regards,

Antonio

[Peony]

O! I've never been using Mapping function before. It's an interesting idea. Thank you.