Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Cant get script below to work :-
Source_Data:
LOAD [Report No] as rpt_num,
capitalize([Requested by]) as req_by,
capitalize(Department) as Dept,
date([Date Requested]) as date_req
FROM
(biff, embedded labels, table is [Completed Ad-hoc jobs$])
where today()-date([Date Requested])<=365
;
Data_sort:
LOAD rpt_num,
count(rpt_num) as Report_no
resident Source_Data
group by req_by
;
Am trying to run a report to count business reports and count how many times an individual makes a request.
Hi,
I think the problem is in your table Data_sort. You must use agregation with GROUP BY.
Try to add Only(rpt_num) as rpt_num).
If this doesn't work, can you give more detail of your issue ? Error in script or not working as you wish ?
I tried this :-
Source_Data:
LOAD [Report No],
capitalize([Requested by]) as req_by,
capitalize(Department) as Dept,
date([Date Requested]) as date_req
FROM
(biff, embedded labels, table is [Completed Ad-hoc jobs$])
where today()-date([Date Requested])<=365
;
Data_sort:
LOAD [Report No],
count([Report No]) as Report_no
resident Source_Data
group by req_by
and got error :-
Hi,
Check with this
Source_Data:
LOAD [Report No],
capitalize([Requested by]) as req_by,
capitalize(Department) as Dept,
date([Date Requested]) as date_req
FROM
(biff, embedded labels, table is [Completed Ad-hoc jobs$])
where today()-date([Date Requested])<=365
;
Data_sort:
LOAD req_by,
[Report No],
count([Report No]) as Report_no
resident Source_Data
group by req_by,[Report No];
Celambarasan
Hi,
You can only able to use the Field names directly(without aggregate functions) when its is specified in a group by clause otherwise you need to use inside Aggregate Functions.
Hope you trying to achieve Number of report numbers in request by category for that check the script below
Data_sort:
LOAD req_by,
count([Report No]) as Report_no
resident Source_Data
group by req_by;
Celambarasan
Hi there
Celambarasan is correct.
You can not count a field and present it explicitly. The count would be 1 pre line.
When using GroupBy clause all fields must be the result of an aggregation function, or present in the group by statement. That is the error you are getting.
What were you attempting to accomplish ??