Which tool do I need? Break up a column into multiple concatenated lists.

I have a list of about 2 million VIN numbers. I want to concatenate those VINs into a character string in the format of "VIN";"VIN";"VIN";"VIN";"VIN" in 1500 increments. I have the data set at the end of the workflow with a column of VINs that are already in the "VIN"; format. How do I concatenate these in 1500 long string variable?

Datasets

Accepted answers

KOBoyle

If I understand your question correctly, you can use the Transform:Summarize tool. For VIN, under Action, select String > Concatenate. With this option, you can specify Start, Separator and End characters for each summarized set of concatenated values. If you do not have a field to Group By that gives you the 1500 record sets, you could potentially use the Tile tool.

Summarize-Concatenate String Data.png

Claje

Hi,

I've created a dummy workflow with VIN as a placeholder value to show how you might accomplish this. I built it using the Generate Rows tool to create 60,000 records as a test.

claje_concatgroupedrecords.yxmd

All comments

danrh

I would suggest something along these lines:

Add a RecordID (starting value of 0), then divide that RecordID by 1500 and round down, then Summarize, grouping by the RecordID column and concatenating the VIN numbers. This should put 1500 on each row.

Hope it helps!

solution.yxmd

KOBoyle

Summarize-Concatenate String Data.png

Claje

Hi,

I've created a dummy workflow with VIN as a placeholder value to show how you might accomplish this. I built it using the Generate Rows tool to create 60,000 records as a test.

claje_concatgroupedrecords.yxmd

CandidDataScientist

Hi @Claje. It seems the workflow is cutting off the values. I should have about 1500 vins per group and I'm getting 14 in each concatenation. Anyway around that?

Claje

That's unusual - does that happen if you try running my attached workflow as well?

Are you seeing this in the Results screen, or in a final output?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335