Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tabisen1
Contributor II
Contributor II

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:

TABLE 1TABLE 2Header 3
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:

11 Replies
luis_pimentel
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

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

tabisen1
Contributor II
Contributor II
Author

can any one help me to solve this issue.

juleshartley
Specialist
Specialist

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
Contributor II
Contributor II
Author

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

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

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
Contributor II
Contributor II
Author

Dear Luis,

I have tried this expression it is still not working

luis_pimentel
Partner - Creator III
Partner - Creator III

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.