Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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()
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
Hi Tyagi,
Could you please post me the above conditions here.
Thanks in advance.
Regards
Srinivas
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