topic Re: Create a new tempary table form 3 tables in QlikView

Create a new tempary table form 3 tables

tabisen1 — Mon, 03 Feb 2014 08:30:20 GMT

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:

Re: Create a new tempary table form 3 tables

luis_pimentel — Mon, 03 Feb 2014 08:40:21 GMT

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;

Re: Create a new tempary table form 3 tables

tabisen1 — Mon, 03 Feb 2014 09:02:55 GMT

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

Re: Create a new tempary table form 3 tables

tabisen1 — Mon, 03 Feb 2014 10:19:44 GMT

can any one help me to solve this issue.

Re: Create a new tempary table form 3 tables

juleshartley — Mon, 03 Feb 2014 10:34:05 GMT

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

tabisen1 — Mon, 03 Feb 2014 10:41:16 GMT

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

luis_pimentel — Mon, 03 Feb 2014 11:16:42 GMT

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

juleshartley — Mon, 03 Feb 2014 11:24:20 GMT

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: Create a new tempary table form 3 tables

tabisen1 — Mon, 03 Feb 2014 12:11:22 GMT

Dear Luis,

I have tried this expression it is still not working

Re: Create a new tempary table form 3 tables

luis_pimentel — Mon, 03 Feb 2014 12:16:07 GMT

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: Re: Create a new tempary table form 3 tables

tabisen1 — Mon, 03 Feb 2014 12:45:10 GMT

Dear

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

Mon, 03 Feb 2014 13:38:24 GMT

Hi Tarun, Use Rangesum in the Sum of Score column

Please find the attached qvw.