2 Replies Latest reply: May 22, 2012 3:57 PM by Masha Aleinikova

# Sum based on dynamic key between two tables

Hello everyone,

I have two tables in QlikView that are NOT connected through a key:

1. Warranty Data

WarrantyIDWarrantyCaseProductMarketCosts
1Defect DisplayAUSA100
2Defect DisplayBGermany100
3Broken Power SupplyACanada200

2. Production Data, already aggregated by product and market

P_ProductP_MarketVolume
AUSA1000
AGermany5000
ACanada2000
BUSA1500
BGermany2500

Now, I want to calculate a relative frequency of occurence of the selected warranty case in the respective country based on the aggregation level the user wants to see (Product, Market or Product x Market etc.)

Example1:

I have a warranty case for product A. Now I need to find the corresponding production volume of product A in ALL markets and devide 1/ProductionVolume.

Example2:

I have a warranty case for Product A in the USA. Now I need to find the corresponding production volume of that product in the USA and devide 1/ProductionVolume(USA).

How can I "extract" the corresponding production volume of e.g. Product A (All Markets) or Product A (e.g. USA)?

My idea was to generate a "dynamic Key" in the Warranty and Production Table based on the aggregation level the user selects:

KeyWarranty = if ( len ([W_PRODUCT]) > 0, [W_PRODUCT], '') & if ( len ([W_MARKET]) > 0, [W_MARKET], '' )

KeyProduction = if ( len ([P_PRODUCT]) > 0, [P_PRODUCT], '') & if ( len ([P_MARKET]) > 0, [P_MARKET], '' )

and then perform the following calculation in the warranty table (did NOT work)

1 / sum( if ( KeyWarranty=KeyProduction), Volume)

Any suggestions on this problem?

Cheers

Sebastian

• ###### Sum based on dynamic key between two tables

Can't you just connect the 2 fields on the product in your load script?  That way you find information about each product from both tables when the user selects a product.

Just rename the fields to be the same in the laod script and Qlikview will automatically join them.

• ###### Re: Sum based on dynamic key between two tables

Hi Sebastian,

You can make a data island with a list of values Product, Market and Product x Market and let the user select one of values in a list box.

UserSelection:

LOAD UserSelection Inline [

UserSelection

Product

Market

Product x Market

];

Your chart expression will then look like that:

if (only(UserSelection) = 'Product',

sum({<P_Product = P(Product)>} Volume),

if (only(UserSelection) = 'Market',

sum({<P_Market = P(Market) >} Volume),

sum({<P_Product = P(Product), P_Market = P(Market) >} Volume)

)

)

/Masha