YashBadiani
Programmer
Scenario
********
I have data from two different data providers which are from two different universes.
The data in the two providers looks something like below :-
Please note that the columns are separated by pipes(|).
Data provider 1(DP1)
====================
ActNum | RepCd| StCde1
----------------------
123456 | YB21 | IL
123460 | YC45 | NC
Each account can be associated with multiple representative codes(RepCd) and a single StCde1.
Data provider 2(DP2)
====================
Each representative(RepCd) is licensed for a list of states.
RepCd | StCde2
----------------------------------------------------
YB21 | MI
YB21 | US
YB21 | NY
YB21 | NJ
YC45 | MI
YC45 | NC
What is needed
--------------
I need to check for each account(ActNum),each representative code(RepCd)in DP1, whether StCde1 is present in list of StCde2
for each RepCd.
For ex :- Account number 123456 is associated with rep code YB21 and StateCode IL. I need to check whether IL is in the list of states for YB21 . In the above example YB21 does not have IL in its list in Data provider 2. Hence i should show this record in Data provider 1.
But for account 123460 which is associatied with NC and rep code YC45, the record should not be shown because NC is present in the list
of StCde2 for YC45.
To summarize, for each RepCd in the first data provider i need to check whether the corresponding StCde1 is present
in the list of StCde2 for the same RepCd code in DP2.
The common dimension between the two data providers is the RepCd.
Final Output
------------
ActNum | RepCd| StCde1
----------------------
123456 | YB21 | IL
Please let me know if the above is possible in BO.
If yes, then please let me know the detailed approach of how to go about it.
Any pointers to solve the above would be of great help. Hope the requirements are clear from the above.
Thank you in advance,
Yash
********
I have data from two different data providers which are from two different universes.
The data in the two providers looks something like below :-
Please note that the columns are separated by pipes(|).
Data provider 1(DP1)
====================
ActNum | RepCd| StCde1
----------------------
123456 | YB21 | IL
123460 | YC45 | NC
Each account can be associated with multiple representative codes(RepCd) and a single StCde1.
Data provider 2(DP2)
====================
Each representative(RepCd) is licensed for a list of states.
RepCd | StCde2
----------------------------------------------------
YB21 | MI
YB21 | US
YB21 | NY
YB21 | NJ
YC45 | MI
YC45 | NC
What is needed
--------------
I need to check for each account(ActNum),each representative code(RepCd)in DP1, whether StCde1 is present in list of StCde2
for each RepCd.
For ex :- Account number 123456 is associated with rep code YB21 and StateCode IL. I need to check whether IL is in the list of states for YB21 . In the above example YB21 does not have IL in its list in Data provider 2. Hence i should show this record in Data provider 1.
But for account 123460 which is associatied with NC and rep code YC45, the record should not be shown because NC is present in the list
of StCde2 for YC45.
To summarize, for each RepCd in the first data provider i need to check whether the corresponding StCde1 is present
in the list of StCde2 for the same RepCd code in DP2.
The common dimension between the two data providers is the RepCd.
Final Output
------------
ActNum | RepCd| StCde1
----------------------
123456 | YB21 | IL
Please let me know if the above is possible in BO.
If yes, then please let me know the detailed approach of how to go about it.
Any pointers to solve the above would be of great help. Hope the requirements are clear from the above.
Thank you in advance,
Yash