How to group specific values in a table?

[#1]

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:

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

[swuehl]

Maybe like this? I calculated also the sum of quantities in the script (do you need it to be selection sensitive?)

Script looks like this:

test:

LOAD Month,

     Product,

     Destination,

     Type,

     Month&Product&Destination as Link,

     price

FROM

teste.xlsx

(ooxml, embedded labels, table is Price);

Testex:

LOAD Link as Link,

     Quantity

FROM

teste.xlsx

(ooxml, embedded labels, table is Quantity);

test2:

Load Link, sum(Quantity) as SumQuantity resident Testex group by Link;

test3:

left join

Load

Link as Link,

avg(price) as average

Resident test

group by Link;

Hope this helps,

Stefan

[#3]

Hi, I have tested with Load Distinct... any other suggestion?

[swuehl · Accepted solution]

Sorry,

I did not get your point. You tested with Load Distinct?

Since I have not used a load distinct (as far as I remember and can see from my post), what do you want to tell me?

If my above code does not work for your problem, please specify, what the exact problem is.

Regards,

Stefan

[#5]

but why dont you use sum(Quantity) * avg(price) as expresion?

[#6]

or if you want to calculate average price in script:

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

test:

LOAD Month,

     Product,

     Destination,

     Type,

     Month&Product&Destination as Link,

     price

FROM

teste.xlsx

(ooxml, embedded labels, table is Price);

Testex:

LOAD Link as Link,

     Quantity

FROM

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

(ooxml, embedded labels, table is Quantity);

left join

Load

          Link as Link,

          avg(price) as average

Resident test

group by Link;

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