topic Any idea why this wont work? in QlikView

Any idea why this wont work?

Mon, 16 Apr 2012 10:03:55 GMT

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.

Any idea why this wont work?

christophebrault — Mon, 16 Apr 2012 10:10:49 GMT

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 ?

Any idea why this wont work?

Mon, 16 Apr 2012 12:50:10 GMT

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

Any idea why this wont work?

CELAMBARASAN — Mon, 16 Apr 2012 13:08:15 GMT

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

Any idea why this wont work?

CELAMBARASAN — Mon, 16 Apr 2012 13:11:23 GMT

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

Any idea why this wont work?

etay_netintent — Mon, 16 Apr 2012 13:50:10 GMT

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