Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

tabisen1
New Contributor

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:

Tags (2)
11 Replies
luis_pimentel
Contributor III

Re: Create a new tempary table form 3 tables

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
New Contributor

Re: Create a new tempary table form 3 tables

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

tabisen1
New Contributor

Re: Create a new tempary table form 3 tables

can any one help me to solve this issue.

juleshartley
Valued Contributor

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?


tabisen1
New Contributor

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

luis_pimentel
Contributor III

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

juleshartley
Valued Contributor

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

tabisen1
New Contributor

Re: Create a new tempary table form 3 tables

Dear Luis,

I have tried this expression it is still not working

luis_pimentel
Contributor III

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.

Community Browser