topic Re: error with group by in QlikView

error with group by

alec1982 — Tue, 31 Jul 2012 21:55:14 GMT

Hi,

I have the following table:

LOAD

RecordId as %LeaseKey,

Sum(CustomFieldValue) as PlHeadcount,

If(CustomFieldValue > 1, 1, 0) as PlIsTreatAsHeadquarters

RESIDENT

tblCustomFieldValue

WHERE

CustomFieldLabel='Headcount (Current)'

Group by %LeaseKey ;

I am getting an error that cannot find %LeaseKey.

Anybody can help.

Thxs,

Re: error with group by

Jason_Michaelides — Tue, 31 Jul 2012 22:27:54 GMT

That's because %LeaseKey doesn't exist yet. Try grouping by RecordId.

Hope this helps,

Jason

Re: error with group by

alec1982 — Tue, 31 Jul 2012 22:46:58 GMT

I got this error.

Aggregation expressions required by GROUP BY clause

thanks,

Re: error with group by

alec1982 — Tue, 31 Jul 2012 23:27:06 GMT

I tried as follow and it didnt work It shows script error but it doesnt tell me what the error.

JOIN (Lease)

LOAD

RecordId as %LeaseKey,

Sum(CustomFieldValue) as PlHeadcount,

If(CustomFieldValue > 1, 1, 0) as PlIsTreatAsHeadquarters

RESIDENT

tblCustomFieldValue

WHERE

CustomFieldLabel='Headcount (Current)'

Group by RecordId

Any help.

Thanks,

Alec

Re: error with group by

Anonymous — Wed, 01 Aug 2012 02:19:26 GMT

I think you'll need to sum up the CustomFieldValue in your if statement as well.

If(sum(CustomFieldValue) > 1, 1, 0) as PlIsTreatAsHeadquarters

Re: error with group by

Wed, 01 Aug 2012 03:04:45 GMT

Hi Alec

You need to indicate in the Group by statement all non aggregated field. try this script instead:

JOIN (Lease)

LOAD

RecordId as %LeaseKey,

Sum(CustomFieldValue) as PlHeadcount,

If(CustomFieldValue > 1, 1, 0) as PlIsTreatAsHeadquarters

RESIDENT

tblCustomFieldValue

WHERE

CustomFieldLabel='Headcount (Current)'

Group by

RecordId,

If(CustomFieldValue > 1, 1, 0)

Regards,

Bernardine

Re: error with group by

alec1982 — Wed, 01 Aug 2012 05:03:48 GMT

Hi,

Thank you for the replay.

I have tried the above script. I didnt get any error but I see that the records have increased. I used to have 2178 records and now I have 2116.

so some of the records were not grouped. I have noticed that any record that has one of the value = 1 is not grouped.

Record ID HeadCount

84212 1

84212 277

Any solution?

Thxs,

Alec,

Re: error with group by

Wed, 01 Aug 2012 05:23:41 GMT

Alec,

Maybe you should check on the If condition. You may want to sum it first before checking. Try this script:

JOIN (Lease)

LOAD

RecordId as %LeaseKey,

Sum(CustomFieldValue) as PlHeadcount,

If(Sum(CustomFieldValue) > 1, 1, 0) as PlIsTreatAsHeadquarters

RESIDENT

tblCustomFieldValue

WHERE

CustomFieldLabel='Headcount (Current)'

Group by

RecordId

let me know if this works.. if not kindly send me your file so i can check.

Regards,

Bernardine

Re: error with group by

alec1982 — Wed, 01 Aug 2012 06:01:52 GMT

Thanks! that fixed the issue. I even didn't think about that.

I appreciate your help.

Best,

Re: error with group by

Wed, 01 Aug 2012 06:44:43 GMT

very much welcome!