Hello - I am trying to convert an excel file into a database file and the data looks like in the left image below. I have set up a workflow to select records for under each of the location (Camp Springs, Largo, etc) and then manipulate the data to make it look the way I want it to. The way I am doing it right now is per the image below: Is there a way for me to avoid repeating the select records process and use a tool (sort of like a macro) that can select records at specified intervals? Thanks a lot in advance.
Best,
Jo
Solved! Go to Solution.
Hi!
You've posted a image of your sample data but if you could attach it as an xlsx file that would make it much easier to help you! It only has to be a small sample as in the image.
Yes there is definitely a way to make this dynamic, once we have data, I'm sure someone will get an answer to you quickly!
Ben
Thanks @DavidP. Would it work if I had to select records 4-21 and then repeat the select record process for 22-39 and so on?
But a quick example with the following data would be...
Field1 |
Camp Springs |
HMO |
POS |
Capitol Hill |
HMO |
POS |
Use the recordID tool, to give the following table...
Field1 | RecordID |
Camp Springs | 1 |
HMO | 2 |
POS | 3 |
Capitol Hill | 4 |
HMO | 5 |
POS | 6 |
Use a formula tool to devide the recordID by the number of rows in each group, in this example, 3, then use the CEIL function to round this to a whole number...
CEIL([RecordID]/3)
Lets call this new field Group
Field1 | RecordID | Group |
Camp Springs | 1 | 1 |
HMO | 2 | 1 |
POS | 3 | 1 |
Capitol Hill | 4 | 2 |
HMO | 5 | 2 |
POS | 6 | 2 |
I will then use a sample tool to return my the first record per group. Before, as a seperate stream, using a sample tool to return everything but the first record per group.
Then simply use the join tool on the group field to make your data look something like...
Company | Field1 |
Camp Springs | HMO |
Camp Springs | POS |
Capitol Hill | HMO |
Capitol Hill | POS |
Example workflow attached...
Ben
Thanks @BenMoss. Since my company name is on a different column and product on a different column, would I have to first combine the two? This is how my entire raw data looks like:
And I have attached the formal of my sample output file.
Can I use your solution without combining the two columns? Sorry if my questions sound very ignorant!
Thanks!
Arrr, this it great, sorry I made an assumption with your output data and what the output should look like, now I have both I can help for sure!
This should work perfectly with your expanded dataset too, you just need to add the transpose after the filter.
Example workflow attached.
Ben
Can you send me your input file as well? Thank you so much!!