This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
how to create new column from particular row data.
for example in the attached sheet I have row number 5 "Data Collection 2017-18" from which i wanted to create date column and the value will be 20180331 means last day of march for year 2018 ,if the year is 2016-17 then 20170331 and so on for all the row of that file.
Second In row number 6 which is "My Trust Name" wanted to create new column trust name and the value for that column will be the value of that cell for all the rows of that file ,currently it's My Trust Name.
I wanted to add this column into my exisitng workflow result.
Here's a modification of your original workflow. You were very close. All I added was the YE date and the split to find the Trust name. this was a little tricky since there's nothing to indicate that it is the trust name row. In the end, I went with the row that has "Data Collection" in the row before and Specialty in the row after(after removing null rows)
I would have thought that @jdunkerley79 might be taking some time off to let the rest of us catch up! I guess the 750 prize is too tempting!
The easiest way to do this is to bring the file name with the input tool and pass it all the way along.
The groupings must be set to group by file name as well as group and then should basically work.
You can then use a Join Multiple to bring the extra columns in rather than the Append Field tool.
As the row number needs to reset by file, the select records tool I used doesnt work in this case. So instead I use a multi row formula to create a row number within each file and then filter on this to get the date cell and trust name.
I have created replica of excel and changed the trust name so now i have 2 excel and i am using wildcard to connect both in a single run each excel has 44 rows so in the result 44 rows should have My trust name as a Trust Name and other 44 should have My trust name1 as a Trust Name. total rows should be 88 but i am getting null on both the column.