7 Replies Latest reply: Jul 30, 2015 3:52 AM by mayilvahanan ramasamy RSS

    Comparing

    Stella Patrizio

      I have a field with10 categories. Every category has 10 products, and every product has his own price and the sales revenue.

      Now, for eache category, I want to find the first product by revenue, and compare it with the first product (of the same category) which contains the word "Mom". This has to be done for all categories.

      How can I do this?

      Thanks!

        • Re: Comparing
          Gysbert Wassenaar

          Can you post a qlikview document that demonstrates the problem?

            • Re: Comparing
              Stella Patrizio

              Now, at the moment, I can't. But I'm going to give you an example here.

               

              Suppose we have 3 categories, like pizza, meat and vegetables. For the category 'Pizza', we have the products 'pizza with ham' that costs €5.00, 'pizza with potatoes' that costs €6.00 exc exc.

              In other words, we have this situation:

              FAMILY         PRODUCT              PRICE

              pizza             pizza with ham           €5.00

              pizza             pizza with potatoes   €6.00

              pizza             ....................          ............

              meat               sausages               €4.00

              meat               ..........                    ..........

               

              The most sold pizza is 'pizza with ham'. Now, I want to find the price of the pizza that contains the word 'potatoes' and compare it with the price of 'pizza with ham' (that's, how said before, the pizza with the highest revenue), indipendent of the position of the pizza with potatoes. The comparison has to be done as the price of the pizza with potatoes divided by the price of the first pizza (ham).

              The result has to be a table that contains, for each family: the product with the research I want (in this example: 'potatoes'), the first product with highest revenue (pizza with ham) and the price variation between these products.

            • Re: Comparing
              arjun rao

              Patrizio Stella

              Please provide the sample file with the data. So that folks will understanding easily and provide quick solution. Guessing answers will take time to solve your issue. Thank you.

              • Re: Comparing
                Stella Patrizio

                Here is the example file:

                  • Re: Comparing
                    Gysbert Wassenaar

                    Looks like you can create a field in the script that contains the price of the product with the largest revenue:

                     

                    Data:

                    LOAD

                         FAMILY,

                         PRODUCT,

                         PRICE,

                         Revenue

                    FROM ....

                     

                    JOIN (Data)

                     

                    LOAD FAMILY, FirstSortedValue(PRICE, -Revenue) as PriceOfMaxRevenueProduct

                    RESIDENT Data

                    GROUP BY FAMILY;

                     

                    You can then use an expression to calculate the ratio: =PriceOfMaxRevenueProduct/PRICE

                    • Re: Comparing
                      mayilvahanan ramasamy

                      Hi

                       

                      Try like this

                      ProdTemp:

                      LOAD * INLINE [

                          FAMILY, PRODUCT, PRICE, Revenue

                          Pizza, pizza with ham, 5, 2000

                          Pizza, pizza with sausages, 6, 800

                          Pizza, pizza with potatoes, 6, 1500

                          Pizza, , 5, 1800

                          Meat, chicken, 10, 500

                          Meat, sausages and potatoes, 8, 300

                      ];

                       

                       

                      Left Join

                      Load FAMILY, FirstSortedValue(PRICE, - Revenue) as MaxRevenuePricePerProd Resident ProdTemp

                      Group by FAMILY;

                       

                       

                      Prod:

                      Load *, if(WildMatch(PRODUCT, '*potatoes*'), PRICE / MaxRevenuePricePerProd) as PriceComparision Resident ProdTemp;

                       

                       

                      DROP Table ProdTemp;