topic Re: Join in QlikView

Join

upaliwije — Tue, 01 Sep 2015 05:59:21 GMT

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

Re: Join

sunny_talwar — Tue, 01 Sep 2015 06:03:17 GMT

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?

Re: Join

gautik92 — Tue, 01 Sep 2015 06:03:57 GMT

join should work

Re: Join

upaliwije — Tue, 01 Sep 2015 06:06:49 GMT

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

Re: Join

sunny_talwar — Tue, 01 Sep 2015 06:10:58 GMT

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?

Re: Join

krishna20 — Tue, 01 Sep 2015 06:44:32 GMT

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

Re: Join

jonathandienst — Tue, 01 Sep 2015 07:08:00 GMT

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.

Re: Join

jagan — Tue, 01 Sep 2015 07:10:05 GMT

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.

Re: Join

qlikviewwizard — Tue, 01 Sep 2015 07:10:29 GMT

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)

Re: Join

jonathandienst — Tue, 01 Sep 2015 07:10:35 GMT

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.

Re: Join

jonathandienst — Tue, 01 Sep 2015 07:13:36 GMT

qlikviewwizard wrote:

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

Disagree - the join eliminates the risk of synthetic keys or circular references for the tables under discussion...

Re: Join

upaliwije — Tue, 01 Sep 2015 07:19:24 GMT

Can Pls share code for Apply may in relation to my above script

Re: Join

qlikviewwizard — Tue, 01 Sep 2015 07:21:09 GMT

Hi Jonathan, What I mean is by considering other tables which are not mentioned in the script. Thank you.

Re: Join

qlikviewwizard — Tue, 01 Sep 2015 07:21:45 GMT

Hi,

Can you please share some sample data. Thank you.

Re: Join

upaliwije — Tue, 01 Sep 2015 07:30:34 GMT

Can you please give example script for apply map with POLICY,NO and POLICY PERIOD

Re: Join

krishna20 — Tue, 01 Sep 2015 07:47:14 GMT

Hi,

Try this,

As per my understanding i did this, if anything wrong excuse and experts will guide.

First you need to create Mapping tables in script tab.

Policy_Map:

Mapping Load

POL_POLICY_NO,

PRL_DESCRIPTION

FROM

E:\PRE_BREAKUP.qvd

(qvd);

Period_Map:

Mapping Load

POL_POLICY_NO,

POLICY_PERIOD

FROM

E:\PRE_BREAKUP.qvd

(qvd);

In another tab load your claim table and write the below script.

CLM:

LOAD

POLICY_NO,

ApplyMap('Policy_Map',POLICY_NO,Null()) as PRL_DESCRIPTION,

ApplyMap('Period_Map',POLICY_NO,Null()) as POLICY_PERIOD,

BCOD,

PRD_CODE,

POLICY_PERIOD,

PREMIUM

FROM

E:\premiumC3.qvd

(qvd)

;

Regards

Krishna

Re: Join

sunny_talwar — Tue, 01 Sep 2015 07:53:58 GMT

Krishna -

I feel that if the join is not working on the POLICY_PERIOD, the same issue will cause a problem in the ApplyMap also. I think the first step would be to make sure that POLICY_PERIOD from two sources are same format, and if they are not make them same format.

What do you think about the above?

Best,

Sunny

Re: Join

krishna20 — Tue, 01 Sep 2015 08:08:52 GMT

Hi Sunny,

Yes i feel the same thing. We need to check the Policy_No and Policy_Period in the both tables first. Because, insurance is a digging concept. If anything misses in the script , results will be totally different and client will not satisfies . I suggest

upaliwije‌to attach sample file. Then only we comes to know whether the data is perfect to implement Join or Apply Map() or any other concept.

Regards

Krishna