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 · Accepted solution]

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

[JohnJPS · Accepted solution]

Hi @DataAnalyst,

Just join the tables together on STATE, in order to have "STATE CODE available... and use a Formula tool to (1) replace ID-Number 2 with "999999999" and modify Rate to a string (if not already)... see attached workflow for particulars.  Hope that helps!

[ @jdunkerley79 wins by 7 seconds! LOL.]

[DataAnalyst]

This is perfect.

I just need to prefix an extra 0 , 0010000

[DataAnalyst]

I tweaked your formula to add an additional 0, it the rate output. Thanks for the help