error with group by

[alec1982]

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,

[Jason_Michaelides]

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

Hope this helps,

Jason

[alec1982]

I got this error.

Aggregation expressions required by GROUP BY clause

thanks,

[alec1982]

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

[Anonymous]

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

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

[#6]

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

[alec1982]

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.

like

Record ID     HeadCount

84212          1

84212          277

Any solution?

Thxs,

Alec,

[#8 · Accepted solution]

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

[alec1982]

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

I appreciate your help.

Best,

[#10]

very much welcome!