Read and process first row from each three files then move to second row

[hf14]

Hi

I am doing reconciliation from three inputs, every row is having data of unique RootID, I want to read first row from each file, process it (transform and join data), output the result in a new sheet named after that row's RootID, then move to second row and do the same.

I am unable to implement this in macro. I tried with batch macro but it is duplicating, giving one-to-many records (attached my current test output). Can anyone please help.

[DataNath · Accepted solution]

@hf14 how does this look? In terms of doing this efficiently I think you were on the right lines with a batch macro. Happy to revisit if this doesn't get you any closer to your desired output but seems to work for the example provided:

RootId feeds the control parameter:

Outputs look like so:

[hf14]

Hi @DataNath 

That's exactly what I wanted, thanks so much!

But the only problem is some of my RootIDs are mix of numbers and alphabets, and then filter tool is giving error 'Invalid type in operator =='. 

In my original dataset this field has V_string datatype. when I change to Int then its reading but escaping the alphanumeric RootIDs.

[DataNath · Accepted solution]

No problem @hf14 - happy to help!

Ah I see, I didn't realise the field was a string and so the filters are set up to filter numeric data types i.e. they don't use quotes. When you come to use this, you'll just need to change the 3 filters so that they contain quotes like so (single or double, doesn't matter):

In your Action tool, you'll need to ensure that you're still only replacing the bit inside the quotes and so you definitely need to tick Replace a specific string and only keep the bit that's inside the quotes in the setup workflow, like so:

Have amended the macro, though I did add 3 extra selects to force RootId to be a string for the setup - you can just right click > delete and connect around these as your incoming data will already be a string. Let me know if you run into anymore issues of course!

[hf14]

@DataNath Thanks for the solution! I have another query though

My original dataset has 80 fields, I have to use Join records by Fields.

My file changes sometimes and gives multiple records for a single rootID (attached FileB-v3). I have used loop on OrderID since this is unique here, inside RootID loop. But I'm getting the duplicate record displayed. (attached Output based on RootID). Can this be handled? 

Also I want a different output for these OrderIDs. So i'm running the loop on those three RootIDs. The topmost Summarize is giving result as below, but I want is to be displayed adjacent to each other. Like in the attached desired output.

[hf14]

Hi @DataNath 

In continuation of this workflow, I want the output in a fix template(attached Test template). The values coming should be filled as per the template wherever that field occurs, for each rootID (attached desired template output)

Thanks in advance!