Creating a look-up, how to pull in table value based on Calculated Dimension

[#1]

Hello QV Community!

Need your help. I have 3 tables:

Table 1: Sales

ID	Name	Title	Category	Sales
1	Bob	Rep	Cars	100
2	Sarah	AVP	Cars & Trucks	100

Table 2: Quota

ID	Name	Title	Category	Quota
1	Bob	Rep	Cars	800
2	Sarah	AVP	Cars & Trucks	2000

Table 3:

[Payment]:

Title	Category	% Achieved	Payment
Rep	Cars	12.00%	100
Rep	Cars	13.00%	200
Rep	Trucks	20.00%	350
Rep	Trucks	25.00%	500
AVP	Cars	4.00%	600
AVP	Cars	5.00%	650
AVP	Cars & Trucks	4.00%	700
AVP	Cars & Trucks	5.00%	750

Once I load my tables, I need to create a table that will:

1. Cacluate a % using Sales (Table 1) / Quota (Table 2)

2. Then, take that Calculated %, and find the payment amount that is associated with the calculated value (Table 3). Here would be my resulting table with Payment results:

ID	Name	Title	Category	Sales	Quota	% Achieved	Payment Result
1	Bob	Rep	Cars	100	800	12.50%	200
2	Sarah	AVP	Cars & Trucks	100	2000	5.00%	750

Is there a way to do this in QV?

[#2]

Attached with answer.Please take a look at the back-end for clarity.

Thanks

[its_anandrjs]

Update Now Check the script

Hi,

See the below script for the table loads and after the aggregate table with sum and average fields you get your result in the table as i understand your requirement.

========================

Table1Sales:

LOAD ID,Name,Sales,Title&'_'&Category as Key,Title as NewTitle,Category as NewCategory;

LOAD * INLINE [

    ID, Name, Title, Category, Sales

    1, Bob, Rep, Cars, 100

    2, Sarah, AVP, Cars & Trucks, 100];

Join

Table2Quota:

LOAD ID,Name,Quota,Title&'_'&Category as Key,Title as NewTitle,Category as NewCategory;

LOAD * INLINE [

    ID, Name, Title, Category, Quota

    1, Bob, Rep, Cars, 800

    2, Sarah, AVP, Cars & Trucks, 2000];

Left Join

[Payment]:

LOAD *,Title&'_'&Category as Key;

LOAD * INLINE [

    Title, Category, % Achieved, Payment

    Rep, Cars, 12.00%, 100

    Rep, Cars, 13.00%, 200

    Rep, Trucks, 20.00%, 350

    Rep, Trucks, 25.00%, 500

    AVP, Cars, 4.00%, 600

    AVP, Cars, 5.00%, 650

    AVP, Cars & Trucks, 4.00%, 700

    AVP, Cars & Trucks, 5.00%, 750];

DROP Field Title; 

Final: 

LOAD 

Key,ID,Name,NewTitle,NewCategory,

Sales,

Quota,

(Sales/Quota)*100 as [% Achieved Calculate],

Avg([% Achieved])*100 as [% Achieved],

Avg(Payment) as Payment

Resident Table1Sales

Group By Key,ID,Name,Key,NewTitle,NewCategory,Sales,Quota;

DROP Table Table1Sales;

and then plot the table box or the straight table

[MarcoWedel]

Hi,

one possible solution:

tabSales:
LOAD *
FROM [http://community.qlik.com/thread/117757] (html, codepage is 1252, embedded labels, table is @1);

tabQuota:
LOAD *
FROM [http://community.qlik.com/thread/117757] (html, codepage is 1252, embedded labels, table is @2);

tabPayment:
LOAD *
FROM [http://community.qlik.com/thread/117757] (html, codepage is 1252, embedded labels, table is @3);

Left Join (tabSales)
LOAD Distinct
  ID,
  Name,
  Title,
  Category,
  Quota
Resident tabQuota;

Left Join (tabSales)
LOAD Distinct
  ID,
  Name,
  Title,
  Category,
  Num(Sales/Quota, '##0.00%', '.') as [% Achieved]
Resident tabSales;

Left Join (tabSales)
LOAD Distinct
  Title,
  Category,
  [% Achieved],
  Payment as [Payment result]
Resident tabPayment;

DROP Tables tabQuota, tabPayment;

How would you like non-matching % Achieved-values to be treated?

Payment result in my example is empty because there is no matching value for the calculated 12.50% in the payments table.

hope this helps

regards

Marco

[#5]

Hello,

please check concept from this thread:

http://community.qlik.com/message/499146#499146

regards

Darek

[#6]

Thank you!