Hi Team,
I have data like below:
Data_Set_1
=========
Case_Id | Case Type | AA_Case_Id | AB_Case_Id | AC_Case_Id | AD_Case_Id |
12345 | AB | NULL | 12345 | 53535 | NULL |
67890 | AC | NULL | NULL | 67890 | NULL |
01234 | AA | 01234 | 45346 | NULL | 92376 |
Data_Set_2
=========
Case_Id | Case Type | Working_Team |
12345 | AB | WT001 |
53535 | AC | WT002 |
67890 | AC | WT003 |
01234 | AA | WT002 |
45346 | AB | WT005 |
92376 | AD | WT006 |
Requested OUTPUT:
Case_Id | Case Type | AA_Case_Id | AB_Case_Id | AC_Case_Id | AD_Case_Id | AA_Status | AB_Status | AC_Status | AD_Status |
12345 | AB | NULL | 12345 | 53535 | NULL | NULL | WT001 | WT002 | NULL |
67890 | AC | NULL | NULL | 67890 | NULL | NULL | NULL | WT003 | NULL |
01234 | AA | 01234 | 45346 | NULL | 92376 | WT002 | WT005 | NULL | WT006 |
- All the last 4 status columns should be of derived ones.
- The Case Ids in the data set 2 may not present in dataset 1
- Case Type in dataset 1 will reflect for only one case id and for the rest of the case id's in that row we have to depend on dataset 2 and map both case id and case type to fetch the working team details.
Example for the 1st Row: AB_Status = If AB_Case_id is null then NULL ELSEIF AB_Case_id is not NULL and Case Type = 'AB' then Working_Team
Similar logic to be built for AC_Status as well but AC_Case_Id with Case Type and Working Team details are present in Data_Set_2 in different rows.
Any help on this will be much appreciated.
Thanks
Dilver
Solved! Go to Solution.
Thanks for your efforts in trying this. But this doesn't solve exactly.
For Instance if you consider row no# 2 - with main case_id '01234' and case type 'AA", it has other multiple case id's in that row which are
AA_Case_Id (01234 - same as main Case_Id),
AB_Case_Id (45346) and
AD_Case_Id (92376)
For this row you got only AA_Status which is 'WT002' and its correct only. But I need other statuses as well in the same row like below
AB_Status - AC_Status - AD_Status
WT005 - NULL - WT006
Note that AC_Status is NULL as we don't have AC_Case_Id in that row. Remaining status we need to fetch from Data Set -2
Please refer to my Output Table in the Question for more clarity.
Your help is much appreciated. Thank You
Thanks & Regards
Dilver
Thanks for the solution @ArtApa . This suits my need. Thank you so much