Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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