Complex String Manipulation - Multiple Excel Table Creation from - Single File

sriniprad08

Hi Team,

Good morning. Hope you are well. I have task which i need help.

Task 1 -

I have three files

A) input file

Mapping file

c) Example - Output File - Part 1

Objective -

a) Map the Input file and Mapping file and Bring the column "B" from Input file to the Mapping file.

b) Next step create the separate tab for each item in "Quiz Question Name".

Please find attached the Example - Output File Part 1

Task 2

a) Using the same Final output file from above task Separate the column "Quiz Response" into "Review" and "Non_Review" and concatenate the results into single row by user and semicolon

Please let me know your thoughts,

Thanks

Input Data.xlsx

Mapping.xlsx

Part1_output example.xlsx

Part2_output template.xlsx

Workflow

Datasets

Input

Reporting

Regex

Best Practices

Dynamic Processing

Data Investigation

Accepted answers

Adrian_T

Hey @sriniprad08, yes that’s possible. In the Select Tool under the “Part 2” Container, go ahead to check the column [Quiz Responses].

All comments

ChrisTX

Can you post your workflow? Is there something that's not working?

If you just don't know where to start, try:

Learn > Academy > Learning Paths

Learn > Academy > Interactive Lessons

At a minimum, you could use two Input Data tools and a Join tool.

Chris

Adrian_T

Hey @sriniprad08,

Here's one way of doing this. Depending on what you would like to see as the output of Part 2, go ahead to amend the final Select Tool before the Output Data Tool.

Output_Part2.xlsx

Output_Part1.xlsx

String Manipulation.yxmd

sriniprad08

Hi @Adrian_T ,

Thank you so much for your time. Looks great.

Can i ask one question please?

How can i output the final data in the below format split into different sheets by "Quiz Question Name".

Cheers

Sri

Adrian_T

Hi @sriniprad08,

This was achieved by configuring the Output Data Tool under "Take File/Table Name From Field" as shown below. Hope this helps! 😎

sriniprad08

Thank you @Adrian_T . Couple of clarification, there are blanks for Due Diligence Red Flags II there is a value in the original data.

Can we have the original text retained if there is no Review or Non Review please?

Thank for the help.

Adrian_T

Hey @sriniprad08, yes that’s possible. In the Select Tool under the “Part 2” Container, go ahead to check the column [Quiz Responses].

sriniprad08

Hi @Adrian_T ,

Thank you so much for the great help. I will get back to you incase of any questions.

Cheers,

Sri

sriniprad08

Hi @Adrian_T ,

Hope you are doing well. Can you please help me with one more thing,

Please find attached the pics,

1) In the first one anything after - Report1_Report3_ should be removed. I have marked as current state and "new" one. - For the FACILITY RANKING

2) In the second one anything before the ._storage management should be retained. - INSPECTION_EXCERPTS_REPORTS

Thanks for the help.

Sri

step11.PNG

step10.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703