Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Joined Field in writing expressions

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

1 Solution

Accepted Solutions
teiswamsler
Partner - Creator III
Partner - Creator III

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

View solution in original post

6 Replies
teiswamsler
Partner - Creator III
Partner - Creator III

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/

Not applicable
Author

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!

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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