I have two tables in QlikView that are NOT connected through a key:
1. Warranty Data
Broken Power Supply
2. Production Data, already aggregated by product and market
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.)
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.
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)