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

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

Thu, 08 May 2014 20:59:09 GMT

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?

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

Thu, 08 May 2014 21:33:26 GMT

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

Thanks

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

its_anandrjs — Thu, 08 May 2014 21:35:35 GMT

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

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

MarcoWedel — Thu, 08 May 2014 22:37:52 GMT

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

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

Fri, 09 May 2014 07:13:40 GMT

Hello,

please check concept from this thread:

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

regards

Darek

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

Sat, 10 May 2014 00:09:12 GMT

Thank you!