Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Using Joined Field in writing expressions

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

6 Replies
Partner
Partner

Re: Using Joined Field in writing expressions

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

Re: Using Joined Field in writing expressions

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

Re: Using Joined Field in writing expressions

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

Re: Using Joined Field in writing expressions

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!

Partner
Partner

Re: Using Joined Field in writing expressions

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

Re: Using Joined Field in writing expressions

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