Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Any ideas why my Group By not working?
[TEST TABLE]:
LOAD
Value,
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
GROUP BY DISTRICT;
DROP TABLE temp_SALES_RXI_WS_MONTH;
Either add Value in your Group By Statement or remove it from the LOAD
1)
[TEST TABLE]:
LOAD
Value,
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
GROUP BY DISTRICT, Value;
DROP TABLE temp_SALES_RXI_WS_MONTH;
2)
[TEST TABLE]:
LOAD
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
GROUP BY DISTRICT;
DROP TABLE temp_SALES_RXI_WS_MONTH;
Either add Value in your Group By Statement or remove it from the LOAD
1)
[TEST TABLE]:
LOAD
Value,
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
GROUP BY DISTRICT, Value;
DROP TABLE temp_SALES_RXI_WS_MONTH;
2)
[TEST TABLE]:
LOAD
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
GROUP BY DISTRICT;
DROP TABLE temp_SALES_RXI_WS_MONTH;
You have another field Value within your load but not in the group by - either you put it into the group by or you removed it (I think the last will be what you wants).
- Marcus
So each field needs to go into the group by?
Hi Lak,
All the fields not in the group by should be aggregated. this is the cause of the issue.
Instead try this:
[TEST TABLE]:
LOAD
Only(Value),
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
GROUP BY DISTRICT;
DROP TABLE temp_SALES_RXI_WS_MONTH;
Whatever isn't getting aggregated needs to be within the Group By statement
because you miss the Value field in the group by
[TEST TABLE]:
LOAD
Value,
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
//GROUP BY DISTRICT; // NO
GROUP BY Value, DISTRICT; // YES
or an aggr function for the field value
[TEST TABLE]:
LOAD
// Value, // NO
sum(Value), // YES
sum(Value_PQ) as [Total Value],
DISTRICT
RESIDENT temp_SALES
GROUP BY DISTRICT;
like this
load
a, b,
sum(c),
count(d)
resident table
where ...
group by
a, b;
Thanks that was a nice easy one.
Hi
I put images on my other question as i couldn't put the file on here. The table expression not working within a KPI.
Thanks