Calculated Column

[mustafaelryah]

Hi All,

I have 2 tables:

1. Maintenance Fee Table:

LOAD MeterSize,

     MaintenanceFee

FROM

(ooxml, embedded labels, table is [Maintenance Fee]);

2. Liabilities Table:

LOAD AccountID,

     POD,

     Balance,

     Name,

     PreviousBalanceValue,

     DisconnectionStatus,

     TypeCategory as CategoryID,

     LastBill,

     NoOfBills,

     if(NoOfBills <= 3,1,if(NoOfBills <= 6, 2,if(NoOfBills <= 12,3,4))) AS LiabilityStatus,

     LastPaymentAmount,

     MRU,

     MeterSize,

    If(LastBill = MaintenanceFee,0,1) AS ActiveStatus

FROM

(ooxml, embedded labels, table is Liabilities);

the 2 table Above connected via MeterSize

and I have calculated Column is (ActiveStatus  ) depending on these tables

but the calculated column gives me error message that can't find the MaintenanceFee field ??

Can you help me plz.

Regards.

[robert_mika]

The error comes that you are not refer to the first table

You probably need Lookup

Never look down – The Lookup() Story

[mustafaelryah]

Thanks Robert this exactly what I need, but when I try to use it and still give me the error message that field not found.

I use the below statement

if ( Lookup(LastBill,MaintenanceFee,1,Maintenance),1,0) As ActiveStatus

What i need is

if the LastBill = MaintenanceFee(this field is in another Table (Maintenance )) to give me 1 else to give me 0

What is the correct Statement for this plz !!

[ramoncova06]

you are missing the table name,

you could also use mapping for this

mapping

MaintenanceFee:

LOAD  MaintenanceFee,1

FROM

(ooxml, embedded labels, table is [Maintenance Fee]);

2. Liabilities Table:

LOAD AccountID,

     POD,

     Balance,

     Name,

     PreviousBalanceValue,

     DisconnectionStatus,

     TypeCategory as CategoryID,

     LastBill,

     NoOfBills,

     if(NoOfBills <= 3,1,if(NoOfBills <= 6, 2,if(NoOfBills <= 12,3,4))) AS LiabilityStatus,

     LastPaymentAmount,

     MRU,

     MeterSize,

   applymap('MaintenanceFee',LastBill,0) AS ActiveStatus

FROM

(ooxml, embedded labels, table is Liabilities);

[robert_mika]

Try like this:

Master Tables:

LOAD MeterSize,

    MaintenanceFee

FROM

(ooxml, embedded labels, table is [Maintenance Fee]);

Liabilities Table:

LOAD AccountID,

    POD,

    Balance,

    Name,

    PreviousBalanceValue,

    DisconnectionStatus,

    TypeCategory as CategoryID,

    LastBill,

    NoOfBills,

    if(NoOfBills <= 3,1,if(NoOfBills <= 6, 2,if(NoOfBills <= 12,3,4))) AS LiabilityStatus,

    LastPaymentAmount,

    MRU,

    MeterSize,

    If(Lookup('MaintenanceFee','MeterSize',MeterSize,'Master Tables')=Last Bill,0,1) AS ActiveStatus

FROM

(ooxml, embedded labels, table is Liabilities);

[jonathandienst]

Hi

I prefer mapping to lookup:

mapFees:

Mapping LOAD MeterSize,

     MaintenanceFee

FROM

(ooxml, embedded labels, table is [Maintenance Fee]);

Liabilities Table:

LOAD AccountID,

     POD,

     Balance,

     Name,

     PreviousBalanceValue,

     DisconnectionStatus,

     TypeCategory as CategoryID,

     LastBill,

     NoOfBills,

     if(NoOfBills <= 3,1,if(NoOfBills <= 6, 2,if(NoOfBills <= 12,3,4))) AS LiabilityStatus,

     LastPaymentAmount,

     MRU,

     MeterSize,

  If(ApplyMap('mapFees', MeterSize) = LastBill, 0, 1) As ActiveStatus

FROM

(ooxml, embedded labels, table is Liabilities);

If the data set is large, you may want to test this and the previous solution to find which one performs better for you.

HTH

Jonathan