Multi Row & Multi Field ?

[suby]

All,

Sample Data attached.

-  I have an input Data set with four sheets ( Each sheet has different schema)
-  Firstly i want to Identify the Users who falls under AREA = EUROPE (From sheet4)
-  Take their EMP ID,FIRST NAME and LAST NAME.(From sheet4)
-  Go to sheet1 Check the all the rows of Data and in Multiple Columns to see

                     If any of those columns has got a EMP ID value who belongs to AREA  = EUROPE

                     (OR)

                     If any of those columns has got  FIRST AND LAST  NAME who belongs to AREA  = EUROPE                             

                     then  replace those values with Masked Data.
-  Repeat the above for all the sheets (from sheet1 to Sheet4).
-  After masking Output the Result in the same format as Input Data set
-  Provide a separate report on how many rows have been Masked for Each Individual tabs in separate Output File.
-  Provide a separate report on how many columns have been impacted with list of column names and sheet names in separate Output  report.

[danilang]

Hi @suby 

This is a large and complex task for as community website such as this.  You may find someone willing to do the entire thing for you, but you'll be more likely to get help if you start the process yourself and then ask for help with any place you have problems.  Post your workflow(or a sample to illustrate the problem) and also any sample inputs AND expected outputs

Dan   

[suby]

Hi Danilang,

Thanks i appreciate your comments on this and I'm looking for a start and and I'm wondering if there is simple solution that some one could help with in terms of using the Multifield formula.

I've attached sample Input Data in my previous posts attaching the sample output Data.

[Luke_C]

Hi @suby 

You could definitely use a multi-field tool to update the 3 fields in one shot based on if they matched to Europe. It would likely be an IF statement and a Replace() function. The challenge is your data isn't presented in a way that will cleanly let you join it to the employee list to get the area. You'll probably need to do some parsing or cleansing to get to that point. 

Give it a shot and attach your workflow if you run into specific issues. 

[danilang]

hi @suby 

Here's a start In Italics

- I have an input Data set with four sheets ( Each sheet has different schema): Use 4 input tools, one for each sheet
-  Firstly i want to Identify the Users who falls under AREA = EUROPE (From sheet4):  add a filter tool where [Area]= "Europe"
-  Take their EMP ID,FIRST NAME and LAST NAME.(From sheet4): Pass these as control parameters into a batch macro
-  Go to sheet1 Check the all the rows of Data and in Multiple Columns to see: Pass sheet 1 as the data into the batch macro

                     In the Batch Macro

                     If any of those columns has got a EMP ID value who belongs to AREA  = EUROPE:  Use a Multifield tool to replace the text of the multiple columns if _currentField_=EmpID then "MASKED DATA"

                     (OR)

                     If any of those columns has got  FIRST AND LAST  NAME who belongs to AREA  = EUROPE: Use Multifield tools to replace the text of the multiple columns wiht Masked data.  the exact method here will depend on whether the there are multiple First/Last name field pairs          

                     then  replace those values with Masked Data.
-  Repeat the above for all the sheets (from sheet1 to Sheet4).  Repeat for sheet 2 and 3.  Sheet 4 you're comparing to itself 
-  After masking Output the Result in the same format as Input Data set
-  Provide a separate report on how many rows have been Masked for Each Individual tabs in separate Output File.:Have the batch macro return how many rows have at least one with "Masked Data".  Transpose the fields so you only have to look at the Value field.    
-  Provide a separate report on how many columns have been impacted with list of column names and sheet names in separate Output  report.This requirement needs more details

If you run into any specific problems when doing this, post the current version of your workflow with specific questions

Dan