5 Replies Latest reply: Nov 17, 2011 9:50 AM by Ionut Bostan RSS

    How to group specific values in a table?

    Leandro Duarte

      Hi Everybody!

       

      I'm trying to group one table (Prices) with one specific value to be calculated over a volume table.

       

      I have in the table Prices in one month 3 levels (Product, Destination, Type), but I only want Product and Destination and in this table I need an average of the results.

       

      I have tested it via Group by, but the results is not satisfactory yet, calculating the table volumes over prices, the values are being duplicated.

       

      Please see below some details about it:

      Untitled.png

      I have the following script behind:

      //////////////

      test:

      LOAD Month,

           Product,

           Destination,

           Type,

           Month&Product&Destination as Link,

           price

      FROM

      teste.xlsx

      (ooxml, embedded labels, table is Price);

       

       

      test3:

      left join

      Load

                Link as Link,

                avg(price) as average

      Resident test

      group by Link;

       

      Testex:

      LOAD Link as Link,

           Quantity

      FROM

      C:\Users\chduartel\Desktop\Group\teste.xlsx

      (ooxml, embedded labels, table is Quantity);

      /////////////////////////

       

      Could anybody help me to create a simple solution for it? (field average one time vs. sum of quantity)

       

      Please note, the solution of doing the formula avg(average) + sum(Quantity) doesn't work without detailing the months, products and destination.

       

      Please find enclosed the files containing the information.

       

      Thanks in advance,

      Leandro Duarte