Create a new tempary table form 3 tables

[tabisen1]

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:

TABLE 1	TABLE 2	Header 3
Customer	Customer	Customer
Type	Target Quantity	Outstanding amount
Quantity	Month	other 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:

[luis_pimentel]

Table1:

LOAD

     Customer,

     Type,

     Quantity

FROM

[Table1.xlsx]

(ooxml, embedded labels, table is Table1);

Table2:

LOAD

     Customer,

     [Target Quantity],

     Month

FROM

[Table2.xlsx]

(ooxml, embedded labels, table is Table2);

TempTable:

Left Join(Table1)

LOAD

     *

Resident Table2;

NoConcatenate

FinalTable:

LOAD

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

     *

Resident TempTable;

Drop Tables Table1,Table2,TempTable;

[tabisen1]

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

[tabisen1]

can any one help me to solve this issue.

[juleshartley]

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?

[tabisen1]

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

[luis_pimentel]

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

[juleshartley]

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...

[tabisen1]

Dear Luis,

I have tried this expression it is still not working

[luis_pimentel]

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.