Mapping of Data Values

[MONICA_E]

Hello,

As I am a beginner and new to Alteryx tool, I got stuck at this problem while getting the final output for the given data.

Along with the data set are the steps explained in details. Please help me proceed further with the solution.

D_Na	AM_NO	Go_A	G_ef	Cou_P	Ef_D	Ag_T	DO_R
Abc	0			HM	12-10-2010	s	s1
AM 1 TO Abc	1	abc	12-10-2010	HM	10-22-2020	am	s1.am.1
am 5 to Abc	5	abc	12-10-2010	HM	08-07-2021	am	s1.am.5
sow	0			HM	12-08-2018	sw	sw1
qa	0			HM	12-10-2010	s	s2
am 7 to qa	7	qa	12-10-2010	HM	12-10-2012	am	s2.am.7
let	0			HM	03-04-2009	n	n1
rbg	0			PD	12-14-2008	s	s1
AM 3 TO RBG	3	RBG	12-14-2008	PD	12-18-2010	am	s1.am.3
exhb 1 to lyb	1	lyb	12-14-2008	PD	12-14-2009	exhb	s2.exh.1
notives	0			PD	09-12-2007	n	n1
lyb	0			PD	12-14-2008	s	s2
xma	0			PD	04-05-2013	s	s3
hol 2 to rbg	2	RBG	12-14-2008	PD	12-14-2010	sw	s1.sw1.2
abg	0			PD	12-14-2012	sw	sw2
PRN	0			PD	12-09-2007	n	n2

1. for each unique [cou_p ], count 's' in [Agr_T] and mark the count in [DO_R(output)] as 's1' ,'s2' and so on. (This Applies to [Agr_T] aswell where value is"n")

2. if [Ag_T] is "am" then comparing [G_ef] value of 'am' (from Ag_T) with value of 's' from [Ef_D] note: considering [Cou_P] is same. case 1 : if [G_ef] of "am" matches with only one [ Ef_D] of "s" then [Do_R] is obtained by concatenating in the format { [Do_R] of that "s".[ag_T] of "am" .[Am_no] of that "am" } case 2: if [G_ef] of "am" matches with more than one [ Ef_D] of "s" then compare [Go_A] of "am" with [D_Na ] of matched [Ef_D] ,once it matches then [Do_R] is obtained by concatenating{ [Do_R] of that "s".[Ag_T] of "am" .[Am_no] of that "am"} (Applies to [Ag_T] having values "sw", "exhb" as well)

Thanks in Advance 🙂 

[messi007 · Accepted solution]

@MONICA_E,

I have created the first check.

However the second part is not clear for me when you 

if [G_ef] of "am" matches with only one [ Ef_D] of "s" then [Do_R] is obtained by concatenating in the format { [Do_R] of that "s".[ag_T] of "am" .[Am_no] of that "am"

I don't see any 12-10-2020 on the column EF_D but you are concatenating the values on your example.

Below the first part of the workflow:

Hope that helps you to continue with the second part 🙂

Regards,

[MONICA_E]

Hi messi007,

Thank you for helping me find the solution for case 1. I have looked into the dataset and corrected as mentioned. Here is the second case which I would like you to look into it and help me solve it and find the solution.

if [Ag_T] is "am" then comparing [G_ef] value of 'am' (from Ag_T) with value of 's' from [Ef_D] note: considering [Cou_P] is same. case 1 : if [G_ef] of "am" matches with only one [ Ef_D] of "s" then [Do_R] is obtained by concatenating in the format { [Do_R] of that "s".[ag_T] of "am" .[Am_no] of that "am" } case 2: if [G_ef] of "am" matches with more than one [ Ef_D] of "s" then compare [Go_A] of "am" with [D_Na ] of matched [Ef_D] ,once it matches then [Do_R] is obtained by concatenating{ [Do_R] of that "s".[Ag_T] of "am" .[Am_no] of that "am"} (Applies to [Ag_T] having values "sw", "exhb" as well)

2007 n n2

Thank you for your help once again, I am really grateful 🙂