Resident Load

[mcoetzee]

I would like to do a resident load but my script keeps giving me a error. I need to see whom of my clients that have more than one contract and the fees are group are paying less than R250.

Can someone please assist me in this case?

Script:

Debcontract_01:
LOAD
*,
If(DiscountEndDate = 'NoEndDate' and [Discount] = 'Y', 'Investigation') AS [Discount Error or Correct],
MasterTotalExc/PaymentFreq AS [New Monthly Amount],
if(DiscountValue>0, ((DiscountValue / 1.15)/PaymentFreq),0) + (MasterTotalExc/PaymentFreq) AS FinalNewMonthlyFee,
If((if(DiscountValue>0, ((DiscountValue / 1.15)/PaymentFreq),0) + (MasterTotalExc/PaymentFreq)) <= 250,'Less','More') As FinalNewMonthlyFee_Less_R250
Resident Debcontract
;

DROP Table Debcontract
;
RENAME Table Debcontract_01 To Debcontract
;

Debcontract_02: 
LOAD
Distinct CustId,
If(Sum(FinalNewMonthlyFee)<= 250,'Less','More') As ClientMonthlyFee_R250
Resident Debcontract
;

Rename Table Debcontract_02 To Debcontract
;

Error:

Invalid expression
Debcontract_02:
LOAD
Distinct CustId,
If(Sum(FinalNewMonthlyFee)<= 250,'Less','More') As ClientMonthlyFee_R250
Resident Debcontract

[tresesco · Accepted solution]

One quick issue : you are using sum() that would require 'group by' clause here.  Try like:

Debcontract_02:
LOAD
Distinct CustId,
If(Sum(FinalNewMonthlyFee)<= 250,'Less','More') As ClientMonthlyFee_R250
Resident Debcontract Group By CustId;

[mcoetzee]

Thanks, it works