topic Re: Sum based on dynamic key between two tables in QlikView

Sum based on dynamic key between two tables

Mon, 21 May 2012 08:53:27 GMT

Hello everyone,

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

1. Warranty Data

WarrantyID	WarrantyCase	Product	Market	Costs
1	Defect Display	A	USA	100
2	Defect Display	B	Germany	100
3	Broken Power Supply	A	Canada	200

2. Production Data, already aggregated by product and market

P_Product	P_Market	Volume
A	USA	1000
A	Germany	5000
A	Canada	2000
B	USA	1500
B	Germany	2500

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

goldnejea8 — Tue, 22 May 2012 18:25:24 GMT

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

masha-ecraft — Tue, 22 May 2012 19:57:52 GMT

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