Updating Embedded Line by comparing two files

Question

Hi,

I am a beginner to Alteryx and wanted to learn about Embedded lines and updating them. I have two sample input files. File 1 represents a subset of student data with their ID and marks scored in different subjects. The total score of passed subjects column is empty.

File 2 has all student ids along with marks that are above the pass score. I need to compare both files and fill in the empty column in file 1 (Total Score of Passed Subjects) with reference to file 2. (which I was able to execute)

Also, I need to add a string "PASSED" to the respective subjects by comparing with marks in file 2. This field is an embedded string and I am not sure about this.

Note: The order in which marks are arranged (in the embedded lines) are same as the order of Mark1, Mark2, Mark3 column in file2.

I have attached a screenshot of file1 input, file2 input, required output and sample workflow (which I have worked so far), along with the output.

Can somebody help me with solve this?

Thanks

File 1 Input.PNG

Required Output.PNG

Output obtained.PNG

File 2 Input.PNG

Sample Workflow - Embedded Line.yxzp

AngelosPachis · Accepted Answer

Hi @Saratnarendran ,

Sorry I couldn't use your workflow as you haven't attached the data inputs (i.e. those are saved somewhere on your desktop and you can package them with your workflow when sharing your work and you want others to use your data). However the screenshots were helpful so I added everything in text input tool.

If the embedded lines is an issue you can use a text to column tools and split to rows using the delimiter 
. I've added an example to the workflow for you to review. Same goes for if you want to use newlines as a delimiter in a summarize tool. Please review this workflow and let me know if there are any questions at any point, happy to answer them