Resident Load

mcoetzee — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Resident Load

tresB — Thu, 14 Nov 2019 09:23:02 GMT

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;

Re: Resident Load

mcoetzee — Thu, 14 Nov 2019 12:51:47 GMT

Thanks, it works

topic Re: Resident Load in QlikView

Resident Load

Re: Resident Load

Re: Resident Load