Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi, I have tested with Load Distinct... any other suggestion?
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
but why dont you use sum(Quantity) * avg(price) as expresion?
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;
/////////////////////////