11 Replies Latest reply: Feb 3, 2014 8:38 AM by Srikanth P

Create a new tempary table form 3 tables

Hi,

Please help me for creating the new table from the 2 or 3 tables for the Calculation in the Pivot table.

I want to Sum the Score Area wise in the pivot table

Example:

CustomerCustomerCustomer
TypeTarget QuantityOutstanding amount
QuantityMonthother feilds
Area
Invoice Amount
Invoice Date
many more feilds

New table in script

Temp Table

TEMP TABLE
Customer
Actual Quantity (Total Quantity)
Target
Score    (... in this field the Condition (if(Actual Quantity/ Target)>=0.80,1,0)....)

Example 2:

• Re: Create a new tempary table form 3 tables

Table1:

Customer,

Type,

Quantity

FROM

[Table1.xlsx]

(ooxml, embedded labels, table is Table1);

Table2:

Customer,

[Target Quantity],

Month

FROM

[Table2.xlsx]

(ooxml, embedded labels, table is Table2);

TempTable:

Left Join(Table1)

*

Resident Table2;

NoConcatenate

FinalTable:

if (Quantity - [TargetQuantity] >= 0.8, 1, 0) as Score,

*

Resident TempTable;

Drop Tables Table1,Table2,TempTable;

• Re: Create a new tempary table form 3 tables

This script is not working the result is synthetic key error accouters.

• Re: Create a new tempary table form 3 tables

can any one help me to solve this issue.

• Re: Create a new tempary table form 3 tables

I'm not sure I totally understand your problem. Could you explain more?

Why can you not do this in the pivot table expression rather than building a new table in the script?

• Re: Create a new tempary table form 3 tables

Dear Julian,

I have tried in the pivot table but i am not getting what i want.

I want to total numer of customer who achievement is more than 80% in a particular Area

• Re: Create a new tempary table form 3 tables

Without joining the two tables, in the pivot table you just need an expression like:

if (sum(Quantity) - [TargetQuantity] >= 0.8, 1, 0)

Depending if Quantity and TargetQuantity are unique values or not (depending on month...) you will need to change the expressión (   sum(TargetQuantity) instead of just TargetQuantity    )

Hope that helps

• Re: Create a new tempary table form 3 tables

Dear Luis,

I have tried this expression it is still not working

• Re: Create a new tempary table form 3 tables

Could you please share the script where you load table1 and table2? And a sample of the information stored on those tables would be useful too.

• Re: Create a new tempary table form 3 tables

Ok, I would still try to do this in the front end, otherwise you are limited by your dimensions that you have pre-calculated across.

What was your expression in the pivot and do you know why it did not work?

It would use a combination of count and aggr...

• Re: Re: Create a new tempary table form 3 tables

Dear

• Re: Re: Re: Create a new tempary table form 3 tables

Hi Tarun, Use Rangesum in the Sum of Score column