Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator III
Creator III

Sum count in pivot table

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

];

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

2 Replies
antoniotiman
Master III
Master III

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
Creator III
Creator III
Author

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