I want to split the record as below. Appreciate the support.
Existing Record:
Record | WO | District | LeadTech | Service |
1 | A00100, A00101 | North,south | Khan, Lopez | Assess, Replace |
Desired Result:
Record | WO | District | LeadTech | Service |
1 | A00100 | North | Khan | Assess |
2 | A00101 | south | Lopez | Replace |
Solucionado! Ir para Solução.
Hi @jmedidi
In this case, it may help to transpose the data first. This will allow you to then use just one Text to Columns tool (splitting to rows). I added a Formula tool to just trim this field that we split so there is no extra whitespace. I then used a Multi-Row Formula tool to edit the Record column so that we could then use a Cross Tab to get the records back on their own rows.
Hope this helps!
Thanks for quick response.
Will this work if i have entries more than 2 in the cell for eg., north, south, east, west and other entries remain same ?
Hello @jmedidi
If you update the District cell in the Text Input in the workflow attached previously with no updates to the other fields, you will see that the output now contains 4 records as follows:
Hello Kenda,
Actually i have added column Record for illustration purpose only. If i remove the column Record , i am getting an error "Type Mismatch in operator + " at Mutil row formula.
Appreciate your support.
Hi @jmedidi
This is because the Multi-Row Formula tool is actually trying to update the Record field. If you want to get rid of it from your input, you could change the Multi-Row formula tool so that it creates a new field called Record instead (with the same formula as before). This field is necessary because the Cross Tab tool uses it to group the data and separate the rows correctly.
Hope this helps!