Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a senario where my table is as below
Mytable:
LOAD
date,
id,
sales,
kesr,
region,
country,
employee,
order
now i want to do a
count(id) as NewCount
group by
region,
country,
employee,
order
and my final table should also contains other fields as well
as show below
Mytable:
date,
id,
NewCount,
sales,
kesr,
region,
country,
employee,
order
how can i achieve it
X:
LOAD
date,
id,
sales,
kesr,
region,
country,
employee,
order
from
.....;
left join (X)
load
region,
country,
employee,
order,
count(id) as NewCount
resident X
group by
region,
country,
employee,
order;
Result:
LOAD
date,
id,
sales,
kesr,
region,
country,
employee,
order
FROM
...souce...
;
JOIN (Result)
LOAD
region,
country,
employee,
order
count(id) as NewCount
group by
region,
country,
employee,
order
RESIDENT
Result
;
Hi Gysbert,
The above senario is not working in my case
can we do a concatnate instead of join there
You can try this also and one possible way as well
FinalTable:
LOAD
region & country & employee & order as Key
date,
id,
sales,
kesr,
region,
country,
employee,
order
From Table;
Left Join( FinalTable )
Load
region & country & employee & order as Key
count(id) as NewCount
Resident FinalTable
group by
region & country & employee & order ;
Regards
Anand
In your scenario here concatenate is not working because it is same table and you need the aggregation of the data on the some selected values that are four fields
region,
country,
employee,
order
In this way you need on aggregation of the id
Regards,
Anand
No luck anand can i try it in set expression
count(aggr(count)............ some thing like this
Yes you can do this aggregation in the expression also
Ex:-
Count(Aggr(Count ( id ),region,country,employee,order))
But the suggested aggregation on the script level is also correct why it is not working...
Regards
Anand