topic Re: Calculated Column in QlikView

Calculated Column

mustafaelryah — Sun, 29 Mar 2015 22:13:57 GMT

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.

Re: Calculated Column

robert_mika — Sun, 29 Mar 2015 22:30:07 GMT

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

You probably need Lookup

Never look down – The Lookup() Story

Re: Calculated Column

mustafaelryah — Sun, 29 Mar 2015 22:57:10 GMT

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 !!

Re: Calculated Column

ramoncova06 — Sun, 29 Mar 2015 23:10:56 GMT

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);

Re: Calculated Column

robert_mika — Mon, 30 Mar 2015 05:16:48 GMT

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);

Re: Calculated Column

jonathandienst — Mon, 30 Mar 2015 05:24:53 GMT

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