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: 
Not applicable

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


Hello QV Community!

Need your help. I have 3 tables:

Table 1: Sales

IDNameTitleCategorySales
1BobRepCars100
2SarahAVPCars & Trucks100

Table 2: Quota

IDNameTitleCategoryQuota
1BobRepCars800
2SarahAVPCars & Trucks2000

Table 3:

[Payment]:

TitleCategory% AchievedPayment
RepCars12.00%100
RepCars13.00%200
RepTrucks20.00%350
RepTrucks25.00%500
AVPCars4.00%600
AVPCars5.00%650
AVPCars & Trucks4.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:

IDNameTitleCategorySalesQuota% AchievedPayment Result
1BobRepCars10080012.50%200
2SarahAVPCars & Trucks10020005.00%750

Is there a way to do this in QV?

5 Replies
Not applicable
Author

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:

QlikCommunity_Thread_117757_Pic1.JPG.jpg

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

Not applicable
Author

Hello,

please check concept from this thread:

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

regards

Darek

Not applicable
Author

Thank you!