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

    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
          Joshua Goldner

          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 Aleinikova

            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