topic Re: Using Joined Field in writing expressions in QlikView

Using Joined Field in writing expressions

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using Joined Field in writing expressions

teiswamsler — Thu, 25 Aug 2016 09:28:46 GMT

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

Colin-Albert — Thu, 25 Aug 2016 09:35:17 GMT

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

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/

Re: Using Joined Field in writing expressions

Thu, 25 Aug 2016 11:15:57 GMT

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!

Re: Using Joined Field in writing expressions

Thu, 25 Aug 2016 11:20:29 GMT

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!

Re: Using Joined Field in writing expressions

teiswamsler — Thu, 25 Aug 2016 13:09:20 GMT

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

Re: Using Joined Field in writing expressions

Fri, 26 Aug 2016 03:33:13 GMT

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