Join Condition in Qlikview

[konidena]

Hi Team,

I have the below scenario.

Two tables are joining here by using left join. In the final table i am applying on logic. Here is the tables.

TableTotalPremium:

Load

POL_NBR,

POL_EFF_DTE,

Businesssegmentkey,

Wins_Agent

EFFDTE

[..QV\TotalPremium.qvd] (QVD);

Left Join (TableTotalPremium)

Load

POL_NBR,

POL_EFF_DTE,

Winsbranchcode

from [..\Master.qvd](qvd);

Final-table

Load

POL_NBR,

POL_EFF_DTE,

If(BusinessSegmentkey=6, If(Winsbranchcode<>'35' and EFFDTE<20161101, 128, 6),

If(BusinessSegmentkey=3, if(Winsbranchcode<>'D7' and EFFDT<20160715, 124,3), Businesssegmentkey)) asBusinesssegmentkey

Wins_Agent,

EFFDTE,

WinsbranchCode

from TableTotalPremium;

Question:

I have to show Businesssegmentkey as 3 when below condition arrives.

In the table "TableTotalPremium"

Businesssegmentkey=3

and POL_NBR, POL_EFF_DTE is not matching with the POL_NBR, POL_EFF_DT from Master table.

Please let me know if my question is not clear.

Regards

Srinivas

[Anil_Babu_Samineni]

I may understand as i feel, But confusing to reply.

Is there any chance to release V1 for short explanation? Better approach if you would show data and expected result

[tyagishaila]

For

Businesssegmentkey=3 you can use where condition

And

for POL_NBR, POL_EFF_DTE is not matching with the POL_NBR, POL_EFF_DT from Master table

you can apply not Exists()

[konidena]

Hi Anil,

Thanks for your visit at my post.

Here is the explanation.

I have BusinessSegmentKey in my application. this field contains the values 1 to 130. This list not contains 124 and 128 keys.

There is a change from business.

If the BusinessSegmentkey=6 and Wins_Branch_Code<>35 and EFFDTE<20161101 (YYYYMMDD),  then it should return 128 as the BusinessSegmentKey.

If the BusinessSegmentkey=3 and Wins_Branch_Code<>D7 and EFFDTE<20160715 (YYYYMMDD),  then it should return 124 as the BusinessSegmentKey.

All the other BusinessSegement should come as  it is.

So, as per the above rules, i made the if condition in my "Final-table" in my above post.

Note: There is no wins_branch_code in TableTotalPremium. Hence to get the Wins_branch_code, i joined TableTotalPremium table with Master table. what happened is, when BusinessSegmentkey became 124, for the list of policies we dont have matching POL_EFF_DTE. hence we are getting Wins_Branch_Code as blanks

All this join conditions you can understand from my above post. I did the left join based on the columns "POL_NBR, POL_EFF_DT".

I got an idea and currently i am implementing this in my app.

When Wins_Branch_Code is null then i should get 3 (Older one) as BusinessSegmentKey.

I did the Preceding load in my Final_table.

if(BusinessSegmentKey=124, if(Isnull(Wins_Branch_Cd),3,124), BusinessSegmentKey) as BusinessSegmentKey

please let me know if you don't understand this entire discussion.

Regards

Srinivas

[konidena]

Hi Tyagi,

Could you please post me the above conditions here.

Thanks in advance.

Regards

Srinivas

[Anil_Babu_Samineni]

For first Business key like below

Load BusinessSegmentKey, If(BusinessSegmentKey = 124 and Wins_Brnach_Cd = ' ', 3, 124) as BusinessSegmentKey1;

Load *, If(BusinessSegmentkey = 6 and Wins_Branch_Code<>35 and EFFDTE<20161101, '128',

If(BusinessSegmentkey = 3 and Wins_Branch_Code<>D7 and EFFDTE<20160715, '124', 'NA')) as BusinessSegmentKey;

Load * From <Data Source>;

Then Create in list box with the help of BusinessSegmentKey1 then check what are the values are coming up

If not, I assume Application helpful to solve