Any idea why this wont work?

[#1]

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.

[christophebrault]

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 ?

[#3]

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 :-

[CELAMBARASAN]

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

[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

[etay_netintent]

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 ??