Using Joined Field in writing expressions

[#1]

Hi all

Recently I faced a problem and could not find the solution till date.

I have two tables - Jan2016 and RateTable. I used "Inner Join" to pick up "Rates" from RateTable. Now I want to use this "Rates" to write a formula to achieve premium. But I am unable to do so. Can anyone please help me with this?

Script is as follows -

RIPrem: <------- This is not working

load

*,

rangemax([BENEFITSUMASSURED]-100000,0)*(Lookup('Rates','LOOKUPKEY',LOOKUPKEY,'RateTable')) as [RI_Prem];

Jan2016:

LOAD ACTUALCESSIONNO,

    REINSURERID,

    SUBTREATYID,

    POLICYNUMBER,

    CUSTOMERID,

    LANAME,

    GENDER,

    DOB,

    DOC,

    DOEF,

    BENEFITGROUPID,

    PLANCODE,

    Coverageid,

    Segmentcode,

    TERM,

    PPT,

    PAYMENTMODE,

    BENEFITSUMASSURED,

    BENEFITPREMIUM,

    MEDICAL,

    EMR,

    EMRPREMIUM,

    POLICYSTATUS,

    Currentfundvalue,

    Vestedbonus,

    SUMCEDED,

    RENEWALSUMCEDED,

    REPORTEDDATE,

    OTHEREXTRAPREMIUM,

    OTHEREXTRADURATION,

    REINRATIO,

    NEXTDUEDATE,

    FACULTATIVE,

    SUMRETENTION,

    ACCOUNTCODE,

    TRANSTYPE,

    PROCESSINTERVAL,

    SAR,

    RESERVEVALUE,

    TSUMCEDED,

    CALCFROM,

    CALCTO,

    SELECTIONDISCOUNT,

    PREMIUMRATE,

    SUBPREMIUMRATE,

    REINSHARE,

    AMOUNT,

    AGEATENTRY,

    DURATION,

    FININTFROM,

    FININTTO,

    REFERENCEDATE,

    CLAIMDATE,

    CLAIMREASONS,

    CLAIMAMOUNTINSURER,

    CLAIMREPORTEDINSURERDATE,

    CLAIMREPORTEDREINSURERDATE,

    LEAD,

    ANNUALIZED,

    ProposalNo,

    CategoryID,

    LapseDate,

    ReinstatementDate,

    Occupation,

    Smoker,

    CurrencyCode,

    [NB/RB],

    [Year of DOC],

    [Month complete],

    Duration,

    [Benefit Type],

    [AGEATENTRY]&'-'&[GENDER] AS [LOOKUPKEY]

FROM

(ooxml, embedded labels, table is ULIPS);

Inner Join(Jan2016)

RateTable:

LOAD LOOKUPKEY,

    Rates

FROM

(ooxml, embedded labels, table is Rates);

[teiswamsler · Accepted solution]

hi Abnik

The flow in the script is a little wrong.

try this

1. Make Jan2016 table

2. join Ratetable

3. Make lookup table (resident)

4. Drop Jan2016 table.

/Teis

[Colin-Albert]

Try using a mapping table and applymap rather than a join.

RateMap:

MAPPING LOAD

     LOOKUPKEY,

         Rates

FROM

(ooxml, embedded labels, table is Rates);

Then you can use

     applymap ('RateMap', LOOKUPKEY, 1) as Rate

     or

     applymap ('RateMap',[AGEATENTRY]&'-'&[GENDER], 1) as Rate

in your load statement.

See these blogs for more details:

Don't join - use Applymap instead

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

[#4]

Thanks Ties for prompt reply.

I am struggling with proper syntax for point 3 in your response.

After 1 and 2, for 3 and 4 I wrote something like this -

RIPrem:

Load

*,

([BENEFITSUMASSURED]*0.5) * (LOOKUP('Rates','LOOKUPKEY',LOOKUPKEY,'RateTable') as [RI_Prem]

Resident 'Jan2016' ;

drop table 'Jan2016'

However, by doing this, I am getting sum of [RI_Prem] as 0 which is not the desired result.

Can you please let me know where possibly I am doing mistakes.

Thanks!

[#5]

Hi Colin

Thanks a ton for your reply.

I am aware that ApplyMap is a much better function to use here. With ApplyMap, I am able to get desired answers. However, I was just curious to learn join queries and then use lookup functions.

Thanks!

[teiswamsler]

Hi abnik

Can you explain what u wanna achieve with the lookup formula?

try this in step 3.

NoConcatenate

RIPrem:

Load

*,

( [BENEFITSUMASSURED]*0.5) * Rates AS [Ri_Prem]

Resident 'Jan2016' ;

drop table 'Jan2016';

/Teis

[#7]

Thanks Teis

It worked smoothly. I was not sure whether using Rates directly will work or not, therefore was using lookup function.

Thanks again!

Abhik