Join

[upaliwije]

I have to join two QVD files matching two Fields. Is it possible ?

CLM:

LOAD

        POLICY_NO,

         BCOD,

         PRD_CODE,

         POLICY_PERIOD,

         PREMIUM

FROM

E:\premiumC3.qvd

(qvd)

;

join(CLM)

LOAD

POL_POLICY_NO as POLICY_NO, 

POLICY_PERIOD ,

PRL_DESCRIPTION,

     PPR_PREMIUM,

     PPR_PERCENTAGE

  FROM

E:\PRE_BREAKUP.qvd

(qvd)

Colured in Red are two fields to match before joining table. The above script does not work  Pls advise me

[sunny_talwar]

When you say it doesn't work what exactly you mean? Doesn't give the desired output? Or gives you an error when you reload?

[gautik92]

join should work

[upaliwije]

Does not give desired output. Records from the second table missing. But If join only with Policy No It works

[sunny_talwar]

You need to make sure that Policy Period's format has a exact match between the two tables. If they don't then you won't get your desired result. Can you share the formats of Policy Period from the two tables?

[krishna20]

Hi,

In my point of view it's better you to approach Apply Map() instead of Join. May be by using join you will get duplicate policy No's in the resultant. So go for Apply Map(). else share sample app.

Reagrds

Krishna

[jonathandienst]

The problem with applymap here is that you will need to construct 3 mapping tables with a composite key in each, and you will need to include the same composite key calculation and 3 ApplyMaps in the CLM table as well. You may have reached the point at which the JOIN is simpler and faster* -- but you do need to take care with joining duplicate values.

*test performance of both options with a full data set to determine which one performs better.

[jagan]

Hi,

Check POLICY_PERIOD  format is same in both the tables?  Also if format is same then verify whether you have valid data for this join?

Hope this helps you.

Regards,

jagan.

[qlikviewwizard]

Hi,

Your join will work without any issue.

But If you consider other tables, your may get circular references or synthetic keys.

Please share your desire output with the sample data.

CLM:

LOAD

        POLICY_NO,

        BCOD,

        PRD_CODE,

        POLICY_PERIOD,

        PREMIUM

FROM

E:\premiumC3.qvd

(qvd);

join(CLM)

LOAD

POL_POLICY_NO as POLICY_NO,

POLICY_PERIOD ,

PRL_DESCRIPTION,

    PPR_PREMIUM,

    PPR_PERCENTAGE

  FROM

E:\PRE_BREAKUP.qvd

(qvd)

[jonathandienst]

Applymap...

Also the join in the original question is a full outer join. You cannot fully emulate a full outer join or an inner join with ApplyMap() - which effectively performs a left join.