topic Re: HOW TO MATCH RECORDS FROM 2ND TABLE in QlikView

HOW TO MATCH RECORDS FROM 2ND TABLE

upaliwije — Sun, 14 Sep 2014 09:08:12 GMT

I have following fields in two tables

//table 1

EN:

LOAD

POLICY_NO ,

PRD_CODE,

POL_PERIOD_FROM,

POL_PERIOD_TO,

POL_SUM_INSURED,

ME_CODE,

BCOD,

TRN_DATE AS RISK_DATE,

PREMIUM

FROM

[..\..\QVD\FACT_Table\Sales.qvd]

(qvd);

//table 2

Concatenate(EN)

LOAD

TRA_BRANCH AS BCOD,

TRA_POLICY_NO AS POLICY_NO,

TRN_DATE AS RISK_DATE,

PREMIUM AS RI_PREMIUM,

DATE(FLOOR(TRA_VALID_FR_DT))AS POL_PERIOD_FROM,

date(floor(TRA_VALID_TO_DT)) AS POL_PERIOD_TO

FROM

RI_PREMIUM.QVD

(qvd);

I want to load data from second table if following fields match from the 1st Table

Table 1 Table 2

POLICY_NO = TRA_POLICY_NO

POL_PERIOD_FROM = date(floor(TRA_VALID_TO_DT)) AS POL_PERIOD_TO

Pls someone help me to modify the script

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

Sun, 14 Sep 2014 09:27:17 GMT

HI,

you you should be able to do this by using the exists function. The syntax should be like the following:

where exists(field from table 1,field from table two)

PRoving the the the dates are in the same format in each table the statement should be the same for this also.

thanks

stuart

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

upaliwije — Sun, 14 Sep 2014 09:30:49 GMT

Thanks

Can u pls modify my script and reply as I am unable to understand what U say

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

Sun, 14 Sep 2014 09:41:38 GMT

Code should look like this:

EN:

LOAD

POLICY_NO ,

PRD_CODE,

POL_PERIOD_FROM,

POL_PERIOD_TO,

POL_SUM_INSURED,

ME_CODE,

BCOD,

TRN_DATE AS RISK_DATE,

PREMIUM

FROM

[..\..\QVD\FACT_Table\Sales.qvd]

(qvd);

//table 2

Concatenate(EN)

LOAD

TRA_BRANCH AS BCOD,

TRA_POLICY_NO AS POLICY_NO,

TRN_DATE AS RISK_DATE,

PREMIUM AS RI_PREMIUM,

DATE(FLOOR(TRA_VALID_FR_DT))AS POL_PERIOD_FROM,

date(floor(TRA_VALID_TO_DT)) AS POL_PERIOD_TO

FROM

RI_PREMIUM.QVD

where exists (field from table one,field from table two)

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

robert99 — Sun, 14 Sep 2014 09:52:02 GMT

or try

where exists (POLICY_NUM,TRA_POLICY_NO )

AND EXISTS (POL_PERIOD_FROM,date(floor(TRA_VALID_TO_DT)) );

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

Sun, 14 Sep 2014 10:03:11 GMT

Good point I didn't think of doing it that way

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

robert99 — Sun, 14 Sep 2014 10:05:32 GMT

Yours should work though I think

where exists (POLICY_NUM,TRA_POLICY_NO )

AND EXISTS (POL_PERIOD_FROM,TRA_VALID_TO_DT);

I have done this in the past but I can't remember what way finally worked

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

upaliwije — Sun, 14 Sep 2014 10:52:14 GMT

THANKS ALL

Re: HOW TO MATCH RECORDS FROM 2ND TABLE

Sun, 14 Sep 2014 10:52:41 GMT

If this discussion is completed please can you mark it as answered.

thanks