Changing Data Format

DataAnalyst

I am trying to find a quick workaround to some manual scrubbing thats being done with the below data.

Below are the 2 input files

Input
STATE	ID NUMBER	ID NUMBER 2	Rate	Company Name
AB	3227365	12-345678	1.00%	ABC
CD	3227366	59-2452322	2.50%	EFG
EF	3227367	84-1185682	3.00%	HIJ
GH	3227368	65-0945794	4.00%	KLM
IJ	3227369	84-1218158	4.50%	NOP
KL	3227370	84-1185682	1.00%	XYZ
MN	3227371	65-0161093	2.00%	OOO
OP	3227372	84-1185682	1.00%	PPP

Input 2
STATE	STATE CODE
AB	01
CD	02
EF	03
GH	04
IJ	05
KL	06
MN	07
OP	08

I am looking at generating the below output. The rate in the input file needs to be formatted to 999v9999 example: 1.00% would show as 0010000.

All  data in the ID-NUMBER 2 column will be replaced by 99999999

The ID NUMBER field will contain the same data.

The 2 columns after RATE needs to be 30 characters long

ID NUMBER	ID- NUMBER 2	RATE			STATE CODE
	99999999	010000			01
	99999999	025000			02
	99999999	030000			03
	99999999	040000			04
	99999999	045000			05
	99999999	010000			06
	99999999	020000			07
	99999999	010000			08

Thank you

jdunkerley79

Have attached a quick go doing what I think you want.

I used a MultiField Formula tool to convert 1.00% to 010000. I chose MultiField as it integrates type conversion.

A Standard formula tool replaces ID2

Finally a join to get state.

Not quite sure this was what you meant but let me know

Data Scrub.yxmd