Hello everyone, I am new to alteryx. I have two excel sheets. Here is an example, Sheet 1 MedicinePhaseUnique IDSubsequent IDDiseasePatient GroupLine of Therapya11721Lung Cancer in adults, children or elderlyAdults1sta11721Lung Cancer in adults, children or elderlyAdults1sta11722Lung Cancer in adults, children or elderlyChildren1sta11722Lung Cancer in adults, children or elderlyChildren1sta11723Lung Cancer in adults, children or elderlyElderly1sta11723Lung Cancer in adults, children or elderlyElderly1stc1451Tuberculosis in adultsAdults1stc1451Tuberculosis in adultsAdults1st Sheet 2 MedicinePhaseUnique IDSubsequent IDDiseaseAge GroupLine of Therapyb2 Lung Cancer in adults, children or elderly--b2 Lung Cancer in adults, children or elderly--d2 Tuberculosis in adults-- What I need to do is in the sheet 2 if the disease matches with that in sheet 1, then the columns of Unique Id, Subsequent Id, patient group and line of therapy should be filled as per the data in sheet 1. Also, if the disease matches and there is a Subsequent Id greater than 1 in the sheet 1 then for that disease in sheet 2, records should be created as per the subsequent Id of sheet 1 with all the columns. Can anyone help me to tackle this?

Match, fill and create records

Hello everyone, I am new to alteryx.
I have two excel sheets. Here is an example,

Sheet 1

Medicine	Phase	Unique ID	Subsequent ID	Disease	Patient Group	Line of Therapy
a	1	172	1	Lung Cancer in adults, children or elderly	Adults	1st
a	1	172	1	Lung Cancer in adults, children or elderly	Adults	1st
a	1	172	2	Lung Cancer in adults, children or elderly	Children	1st
a	1	172	2	Lung Cancer in adults, children or elderly	Children	1st
a	1	172	3	Lung Cancer in adults, children or elderly	Elderly	1st
a	1	172	3	Lung Cancer in adults, children or elderly	Elderly	1st
c	1	45	1	Tuberculosis in adults	Adults	1st
c	1	45	1	Tuberculosis in adults	Adults	1st

Sheet 2

Medicine	Phase	Unique ID	Subsequent ID	Disease	Age Group	Line of Therapy
b	2			Lung Cancer in adults, children or elderly	-	-
b	2			Lung Cancer in adults, children or elderly	-	-
d	2			Tuberculosis in adults	-	-

What I need to do is in the sheet 2 if the disease matches with that in sheet 1, then the columns of Unique Id, Subsequent Id, patient group and line of therapy should be filled as per the data in sheet 1.

Also, if the disease matches and there is a Subsequent Id greater than 1 in the sheet 1 then for that disease in sheet 2, records should be created as per the subsequent Id of sheet 1 with all the columns.

Can anyone help me to tackle this?

Accepted answers

grazitti_sapna

Hi @Pramod91,

Below is my attempt to solve your problem:

PFA Workflow.

I hope it solves your issue.

Join_Problem.yxzp

All comments

bpatel

hi @Pramod91 ,

could you please share what your final output should look like?

Pramod91

Hi bpatel,

Thanks for coming back to me. The output I need is - The sheet 2 should be updated like this

Medicine	Phase	Unique ID	Subsequent ID	Disease	Age Group	Line of Therapy	Status
b	2	172	1	Lung Cancer in adults, children or elderly	Adults	1st	Filled taking reference from sheet 1
b	2	172	1	Lung Cancer in adults, children or elderly	Adults	1st	Filled taking reference from sheet 1
d	2	45	1	Tuberculosis in adults	Adults	1st	Filled taking reference from sheet 1
b	2	172	2	Lung Cancer in adults, children or elderly	Children	1st	Record Created and filled taking reference from sheet 1
b	2	172	2	Lung Cancer in adults, children or elderly	Children	1st	Record Created and filled taking reference from sheet 1
b	2	172	3	Lung Cancer in adults, children or elderly	Elderly	1st	Record Created and filled taking reference from sheet 1
b	2	172	3	Lung Cancer in adults, children or elderly	Elderly	1st	Record Created and filled taking reference from sheet 1

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703