topic Re: String Parsing Question in Discussions

String Parsing Question

modelFit — Sat, 06 Apr 2024 18:54:11 GMT

Dear Community,

I am receiving data with identifying information in 1 cell.

Names Default to Here(1);

dt = New Table("Sample",
add rows(2),
	New Column( "Field_9630", Character, "Nominal" )
);

dt:Field_9630 << Set Values({"Customer: Mr A LoyalProduct: Cabinet HardwareRegion: Suburban New Dev", "Customer: Jenifer B Ashley LeighProduct: LumberRegion: Suburban Existing"});

The target is to produce three columns - Customer, Product, Region

dt2 = New Table( "Sample 2",
	Add Rows( 2 ),
	New Column( "Field_9630",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"Customer: Mr A LoyalProduct: Cabinet HardwareRegion: Suburban New Dev",
			"Customer: Jenifer B Ashley LeighProduct: LumberRegion: Suburban Existing"
			}
		),
		Set Display Width( 132 )
	),
	New Column( "Customer",
		Character,
		"Nominal",
		Set Values( {"Mr A Loyal", "Jenifer B Ashley Leigh"} )
	),
	New Column( "Product",
		Character,
		"Nominal",
		Set Values( {"Cabinet Hardware", "Lumber"} )
	),
	New Column( "Region",
		Character,
		"Nominal",
		Set Values( {"Suburban New Dev", "Suburban Existing"} )
	)
);

Many thanks in advance!

Sincerely,

Re: String Parsing Question

Thierry_S — Sat, 06 Apr 2024 19:21:58 GMT

Hi,

It is always challenging to parse strings when you do not have clear delimiters.

Assuming that the input string will always contain the keywords "Customer:", "Product:", and "Region:", you can use the approach below:

Replace the keywords with "," using the SUBSTITUTE() function
Parse the relevant information using the WORD() function
Clean up the resulting strings with the TRIM() function.

New Table( "Sample_PARSED",
	Add Rows( 2 ),
	New Column( "Field_9630",
		Character,
		"Nominal",
		Set Values(
			{"Customer: Mr A LoyalProduct: Cabinet HardwareRegion: Suburban New Dev",
			"Customer: Jenifer B Ashley LeighProduct: LumberRegion: Suburban Existing"
			}
		),
		Set Display Width( 726 )
	),
	New Column( "TEMP",
		Character,
		"Nominal",
		Formula(
			Substitute( :Field_9630,
				"Customer:", ",",
				"Product:", ",",
				"Region:", ","
			)
		)
	),
	New Column( "Column 2",
		Character,
		"Nominal",
		Formula( Trim( Word( 1, :TEMP, "," ) ) ),
		Set Display Width( 157 )
	),
	New Column( "Column 3",
		Character,
		"Nominal",
		Formula( Trim( Word( 2, :TEMP, "," ) ) )
	),
	New Column( "Column 4",
		Character,
		"Nominal",
		Formula( Trim( Word( 3, :TEMP, "," ) ) ),
		Set Display Width( 200 )
	)
)

I hope it helps.

Best,

Re: String Parsing Question

modelFit — Sat, 06 Apr 2024 19:25:07 GMT

@Thierry_S Thanks for the quick reply and solution. Much appreciated!