Comparing records in multiple rows

[Dilver_Shaik]

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

[MeganDibble]

Hi @Dilver_Shaik ,

Please see the attached workflow--does this answer your question?

[Dilver_Shaik · Accepted solution]

@MeganDibble,

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

[ArtApa · Accepted solution]

Hi @Dilver_Shaik - Here is a solution for you:

[Dilver_Shaik]

Thanks for the solution @ArtApa . This suits my need. Thank you so much